Forum Discussion
Rankx on a matrix
i am trying to do rankx on a matrix table but when i drill down i would like the rankx to recalculate when i drill down, currently i have the following fields in my matrix and want to do the rank by total sales. Any help would be amazing;
8 Replies
Anonymous currently if i am just looking at the table before i drill down from user the ranking is current using;
Rankx(all(username),[Total Sales])
but when i drill down to month it just gives me 1 on everything then when i further drill down to the other options it is always 1 when i would expect it to recalculate and rank the months then rank the next category etc
Hi Anonymous ,
Since it is matrix and you need to drill down, you will need to use ISINSCOPE.
See this article
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
Regards,
Harsh Nathani
- Greg_Deckler
Community Champion
- amitchandak
Super User
Anonymous ,Can you share sample data and sample output in table format?
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415 
v-xicaiCommunity Support
Hi Anonymous ,
You may create measure like DAX below .
Endleaf Rank = SWITCH ( TRUE (), ISINSCOPE ( Sales[Username] ), RANKX ( ALLSELECTED ( Sales[Username] ), CALCULATE ( SUM ( Sales[Point] ) ), , DESC, DENSE ), ISINSCOPE ( Sales[Date].[Month] ), RANKX ( ALLSELECTED ( Saless[Date].[Month] ), CALCULATE ( SUM ( Sales[Point] ) ), , DESC, DENSE ), ISINSCOPE ( Sales[store]]] ), RANKX ( ALLSELECTED ( Store[store] ), CALCULATE ( SUM ( Sales[Point] ) ), , DESC, DENSE ), ISINSCOPE ( Sales[Product]]] ), RANKX ( ALLSELECTED ( Store[Product] ), CALCULATE ( SUM ( Sales[Point] ) ), , DESC, DENSE ) )Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
