Forum Discussion
Advocate IIsummarize rows based on a row specific measure like rankx
This is what I need:
The sales amount % of items in the top 20 against the total sales. This must be dynamic, the top20 this week is different from the top20 last week.
So I create a measure:
[sales rank] = RANKX(ALLSELECTED('DIM-Item'),[Sales amt.])
this gets all items ranked based on the applied filters like week or item category.
Now I want to calculate the sales amount of the items with rank < 21
SUMX( FILTER('DIM-Item', [sales rank] < 21), [Sales amt.])
So far so good. Only items in the top20 (rank < 21) get a value in the table.
The problem is the total line.
The total line show the full sales amount. This is because the [sales rank] in the total line = 1. It does not filter the item lines and then summarizes. It calculates the formula on the total line and then the filter for [sales rank] < 21 is not correct.
Please check this table, especially the total line:
Thanks for the help
Kind regards,
Johan
Hi Johan ,
The issue with your original measure is that the total row does not respect the row-level filtering of rank values. At the total level, DAX evaluates the expression as a whole rather than summing filtered individual rows. To fix this, you need to explicitly recreate the ranking per item within a row context and then filter based on that. This can be done using ADDCOLUMNS and RANKX over VALUES, which ensures the filter logic applies uniformly across all rows, including the total. Here’s the corrected DAX:
Top 20 Sales Amt = SUMX( FILTER( ADDCOLUMNS( VALUES('DIM-Item'[Item Code]), "ItemRank", RANKX(ALLSELECTED('DIM-Item'[Item Code]), [Sales amt.]) ), [ItemRank] < 21 ), [Sales amt.] )This version creates a virtual table where each item code is ranked, filters to only the top 20, and then sums their sales amounts. This ensures the total row shows only the sum of sales for top 20 items, not the full total.
Best regards,
4 Replies
v-hashadapuCommunity Support
Hi Johan , Thank you for reaching out to the Microsoft Community Forum.
Based on your description, I took your sample data and worked out the solution.
Please refer attached .pbix file for reference and share your thoughts.
If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.- DataNinja777
Super User
Hi Johan ,
The issue with your original measure is that the total row does not respect the row-level filtering of rank values. At the total level, DAX evaluates the expression as a whole rather than summing filtered individual rows. To fix this, you need to explicitly recreate the ranking per item within a row context and then filter based on that. This can be done using ADDCOLUMNS and RANKX over VALUES, which ensures the filter logic applies uniformly across all rows, including the total. Here’s the corrected DAX:
Top 20 Sales Amt = SUMX( FILTER( ADDCOLUMNS( VALUES('DIM-Item'[Item Code]), "ItemRank", RANKX(ALLSELECTED('DIM-Item'[Item Code]), [Sales amt.]) ), [ItemRank] < 21 ), [Sales amt.] )This version creates a virtual table where each item code is ranked, filters to only the top 20, and then sums their sales amounts. This ensures the total row shows only the sum of sales for top 20 items, not the full total.
Best regards,
JohanThanks! That works. I know the concept of total level context, just couldn't work it out as you did.
- v-hashadapu
Hi Johan , I wanted to check with you and see if the provided information was useful. If any of it helped resolve your question, consider marking it as "Accept as Solution" to make it easier for others to find. Let me know if there's anything else I can assist with!