locked
How to disable Row label from the aggregation function in Pivot table RRS feed

  • Question

  • Hello everyone,

    I have table in Power Pivot like shown below:


    Item_Name Category Vendor Sales_Amount

    Item 1 Category 1 Vendor 1 30

    Item 2 Category 1 Vendor 2 25

    Item 3 Category 2 Vendor 3 50

    Item 3 Category 2 Vendor 3 60

    Item 3 Category 2 Vendor 3 20
    Item 2 Category 1 Vendor 2 10

    Item 2 Category 1 Vendor 2 30

    Item 2 Category 1 Vendor 2 100

    Item 2 Category 1 Vendor 1 20

    By using above table i have to create Rank(Based on Sales amount) by Category and Item name in Pivot table, i have done easily like added two dimension attribute(Category, Item_Name) into row label and Sales_Amount into aggregation tab then I calculated rank:=RANKX(ALL(Sales[Item_Name]),[Sum of Sales_Amount]) so finally pivot table looks like shown below:

        


    But end user want to see the vendor name also in the pivot table but the Rank suppose to be based on Sales amount by Category and Item name. if i added the vendor name also into the row label, rank calculated based on on Sales amount by Category, Item name and vendor.


    I would be really grateful if anyone advise how to fix this problem as it will be helpful my most of the reports.

    Regards,

    Robert 


    Monday, August 11, 2014 3:37 AM

Answers

  • When you said rank by category & item_name I interpreted that as "find all the distinct combinations of category and item_name and rank them". Where as I would interpret your screenshot as "find the rank of item_name within each category".

    And you could implement that second requirement with something like the following:

    Rank4:=RANKX(
      CALCULATETABLE(all(Sales[Item_Name]),values(Sales[Category]))
      ,CALCULATE([Sum of Sales_Amount],ALL(Sales[Vendor]))
    )

    The first CALCULATETABLE finds all the Item_name values in the current category. The second parameter to rank says to return the value of [Sum of Sales_Amount] ignoring the [Vendor] column. Depending on what other columns you have in your real model you may need to experiment with adding extra ALL() filters to the CALCULATE() function or switching to use ALLEXCEPT:

    eg. 

    calculate([Sum of Sales_Amount],ALLEXCEPT(sales,Sales[Item_Name],Sales[Category]))

    like my initial suggestion.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by robert bosco Tuesday, August 12, 2014 7:49 AM
    Tuesday, August 12, 2014 7:16 AM
  • Thanks for your prompt answer.

    Yeah after seen your answer, i realized that my question was wrong. 

    Its really helpful for me.

    Rank5:=RANKX(SUMMARIZE(ALL(Sales),[Item_Name]),CALCULATE([Sum of Sales_Amount],ALLEXCEPT(Sales,Sales[Item_Name],Sales[Category])))

     
    • Edited by robert bosco Tuesday, August 12, 2014 8:00 AM
    • Marked as answer by Elvis Long Thursday, September 11, 2014 8:39 AM
    Tuesday, August 12, 2014 7:54 AM

All replies

  • I think the following should work:

    Rank2:=RANKX(SUMMARIZE(ALL(Sales),[Item_Name],[Category]),CALCULATE([Sum of Sales_Amount],ALLEXCEPT(Sales,Sales[Item_Name],Sales[Category])))

    Using ALLEXCEPT() to calculate the sales amount used for calculating the rank at the ALL level for every column in the sales table except for Item_Name and Category.


    http://darren.gosbell.com - please mark correct answers

    Monday, August 11, 2014 7:07 AM
  • Darren Gosbell,

    Thanks for your reply.


    Item_Name Category Vendor Sales_Amount

    Item 1 Category 1 Vendor 1 30

    Item 2 Category 1 Vendor 2 25

    Item 3 Category 2 Vendor 3 50

    Item 3 Category 2 Vendor 3 60

    Item 3 Category 2 Vendor 3 20

    Item 2 Category 1 Vendor 2 10

    Item 2 Category 1 Vendor 2 30

    Item 2 Category 1 Vendor 2 100

    Item 2 Category 1 Vendor 2 20

    Item 4 Category 1 Vendor 2 3

    Item 4 Category 1 Vendor 2 50

    Item 4 Category 1 Vendor 2 3

    The above is my new source data.

    I used this function to calculate  Rank:=RANKX(ALL(Sales[Item_Name]),[Sum of Sales_Amount])

    and also used yours below:  

    Rank2:=RANKX(SUMMARIZE(ALL(Sales),[Item_Name],[Category]),CALCULATE([Sum of Sales_Amount],ALLEXCEPT(Sales,Sales[Item_Name],Sales[Category])))


    The Preceding screenshot is the result of our two function but i wanna pivot table like shown below:

    Could please help me to fix it out.

    Tuesday, August 12, 2014 6:24 AM
  • When you said rank by category & item_name I interpreted that as "find all the distinct combinations of category and item_name and rank them". Where as I would interpret your screenshot as "find the rank of item_name within each category".

    And you could implement that second requirement with something like the following:

    Rank4:=RANKX(
      CALCULATETABLE(all(Sales[Item_Name]),values(Sales[Category]))
      ,CALCULATE([Sum of Sales_Amount],ALL(Sales[Vendor]))
    )

    The first CALCULATETABLE finds all the Item_name values in the current category. The second parameter to rank says to return the value of [Sum of Sales_Amount] ignoring the [Vendor] column. Depending on what other columns you have in your real model you may need to experiment with adding extra ALL() filters to the CALCULATE() function or switching to use ALLEXCEPT:

    eg. 

    calculate([Sum of Sales_Amount],ALLEXCEPT(sales,Sales[Item_Name],Sales[Category]))

    like my initial suggestion.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by robert bosco Tuesday, August 12, 2014 7:49 AM
    Tuesday, August 12, 2014 7:16 AM
  • Thanks for your prompt answer.

    Yeah after seen your answer, i realized that my question was wrong. 

    Its really helpful for me.

    Rank5:=RANKX(SUMMARIZE(ALL(Sales),[Item_Name]),CALCULATE([Sum of Sales_Amount],ALLEXCEPT(Sales,Sales[Item_Name],Sales[Category])))

     
    • Edited by robert bosco Tuesday, August 12, 2014 8:00 AM
    • Marked as answer by Elvis Long Thursday, September 11, 2014 8:39 AM
    Tuesday, August 12, 2014 7:54 AM