5 Replies Latest reply: Jan 22, 2018 8:30 PM by vidya sagar malla RSS

    Rank Grouping By Dimension expressions

    vidya sagar malla

      Hello Everyone,

       

      I want to rank the products based on the two measures (Sales and Actuals). With this expression, I was getting the following results. But what I was expecting is different. Can you please help me in building the right expression.

       

      =if(aggr(rank(sum([Sales])),[Product ID])<= 3,'Top 3 Product Sales',

      if(aggr(rank(sum([Actuals])),[Product ID])<= 3,'Top 3 Product Actuals'

      ))

       

      Result:

      RankDimensions
      1Top 3 Product Sales
      2Top 3 Product Sales
      3Top 3 Product Sales
      4Top 3 Product Actuals
      5Top 3 Product Actuals
      6Top 3 Product Actuals


      Expecting:

      RankDimensions
      1Top 3 Product Sales
      2Top 3 Product Sales
      3Top 3 Product Sales
      1Top 3 Product Actuals
      2Top 3 Product Actuals
      3Top 3 Product Actuals

       

       

      Question Modified from above:

       

      From the below sample table. I want to create the dimension expression pivot table. I considered using the ranking for this approach but I am not getting the results as expected.

       

      sample data table:  

           

      ProductSales AmountActual AmountRisk ValueReturn Value
      A6052015
      B501002525
      C70705535
      D20351530
      E10251715
      F5302020

       

      Expected Result:

         

      DimensionRisk ValueReturn Value
      Average of TOP 3 Products by Sales Amount33.3325
      Average of TOP 3 Products by Actual Amount31.6630

       

       

       

      Thanks in Advance,

      -Vidya

       

      Message was edited by: vidya sagar malla