Forum Discussion
Rankx - Top 5 values
Hello there!
I have used the below dax expression for Top 5 Managers by Commision, which worked,
Above is the result, which is fine, but there are two problems:
- Total: It shows the total for the whole column, not for the top 5 values. How do I fix this?
- I want to add another column for the store name, which shows the store name of each manager. However, when I add the column, it starts showing the data for all the managers instead of the top 5 managers. How can I add the store name column without ruining the top 5 rank?
Hi Ahsan_ ,
1. The reason you are getting the full total in the total row is because there is no concept of a manager filtering this value so there is nothing to rank. To remedy this you can create a new measure that iterates over the all the values of manager and adds them up.
Top 5 Managers Enhanced = SUMX( VALUES( Store[Manager Name] ), [Top 5 managers] )2. When you add Store onto the axis that adds store to the filter context of the rank. All managers will be the top ranked for their store (assuming one manager per store) so they all show. I don't know the intricacies of the model but you could extend the above to state
Top 5 Managers Enhanced = CALCULATE( SUMX( VALUES( Store[Manager Name] ), [Top 5 managers] ), ALL( Store[Store Name] ) )I've assumed there that Store Name is in the same Store table as manager.
If you want it all in the same measure you could copy paste your original code in but make sure to wrap it in a CALCULATE so that context transition takes place
Top 5 Managers Enhanced = CALCULATE( SUMX( VALUES( Store[Manager Name] ), CALCULATE( VAR ranking = RANKX(ALL(Store[Manager Name]),[Sales Commission],,DESC) RETURN IF(ranking <=5,[Sales Commission]) ) ), ALL( Store[Store Name] ) )Hope it helps,
Kris
3 Replies
- kriscoupe
Solution Supplier
Hi Ahsan_ ,
1. The reason you are getting the full total in the total row is because there is no concept of a manager filtering this value so there is nothing to rank. To remedy this you can create a new measure that iterates over the all the values of manager and adds them up.
Top 5 Managers Enhanced = SUMX( VALUES( Store[Manager Name] ), [Top 5 managers] )2. When you add Store onto the axis that adds store to the filter context of the rank. All managers will be the top ranked for their store (assuming one manager per store) so they all show. I don't know the intricacies of the model but you could extend the above to state
Top 5 Managers Enhanced = CALCULATE( SUMX( VALUES( Store[Manager Name] ), [Top 5 managers] ), ALL( Store[Store Name] ) )I've assumed there that Store Name is in the same Store table as manager.
If you want it all in the same measure you could copy paste your original code in but make sure to wrap it in a CALCULATE so that context transition takes place
Top 5 Managers Enhanced = CALCULATE( SUMX( VALUES( Store[Manager Name] ), CALCULATE( VAR ranking = RANKX(ALL(Store[Manager Name]),[Sales Commission],,DESC) RETURN IF(ranking <=5,[Sales Commission]) ) ), ALL( Store[Store Name] ) )Hope it helps,
Kris
It worked like magic! Thanks
thank you😁
