Create dynamic Ranking measure

[scabral]

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

[Anonymous · Accepted solution]

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

[amitchandak]

@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-measures

Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40