Forum Discussion
Create dynamic Ranking measure
Hi,
have the following table:
| Classification | Division | Operations | Value | Year |
| Healthcare | Eastern | Boston | 1,000,000 | 2021 |
| Helathcare | Western | Los Angeles | 500,000 | 2021 |
| Healthcare | Western | San Francisco | 250,000 | 2021 |
| Manufacturing | Southern | Texas | 100,000 | 2020 |
| Manufacturing | Southern | Florida | 5,400,000 | 2020 |
So each field (except Value) will be a slicer. The ranking will need to be done dynamically depending on what slicers are selected. So if Healthcare is selected, the ranking will take into account the 3 rows for healthcare and rank by value descending. If Healthcare and Los Angeles are selected, then the ranking will be done with just that row. If no slicers selected, than rank entire table.
not sure how to use Rankx function to get this done. So far my results don't bring back what i am expecting.
Scott
Hi scabral ,
The formula should be:
Measure = Rankx(ALLSELECTED('Table'),CALCULATE(Sum('Table'[Value])),,desc,dense)Otherwise it will always return 1.Best Regards,Jay
2 Replies
scabral , Try like
Rankx(allselected(Table[Classification]) , calculate(Sum(Table[Value])) , ,desc, dense)
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measuresMeasure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40
Hi scabral ,
The formula should be:
Measure = Rankx(ALLSELECTED('Table'),CALCULATE(Sum('Table'[Value])),,desc,dense)Otherwise it will always return 1.Best Regards,Jay
