topic Create dynamic Ranking measure in DAX Commands and Tips

Create dynamic Ranking measure

scabral — Fri, 08 Apr 2022 00:58:44 GMT

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

Re: Create dynamic Ranking measure

amitchandak — Fri, 08 Apr 2022 01:26:17 GMT

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

Re: Create dynamic Ranking measure

Anonymous — Wed, 13 Apr 2022 11:47:20 GMT

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