Forum Discussion
Dynamic Ranking
Hi SagarVasaikar ,
The issue happens because your current RANKX is including Franchise Group in the filter context. When you slice on Franchise, the ranking logic only sees that subset, so the supplier order breaks.
To fix this, you need to rank suppliers globally by spend (ignoring Franchise) while still keeping other slicers active.
Try the bellow measure:
Rank = RANKX (ALL ( Sheet1[SupplierName]), CALCULATE (SUM ( Sheet1[Sum of Spend] ), REMOVEFILTERS ( Sheet1[Franchise Group] )),,DESC, DENSE
If you also want to dynamically show Top N suppliers.
Create a What-If parameter table (TopN[Value] = 1..20).Add this measure.
Show TopN = IF ( [Rank] <= SELECTEDVALUE ( TopN[Value] ), 1, 0 )
Use Show TopN = 1 as a visual filter on your 100% stacked bar.
This way, your stacked bar will always sort suppliers correctly, even with multiple slicers applied.
Thanks,
Akhil.
Hi SagarVasaikar ,
The issue happens because your current RANKX is including Franchise Group in the filter context. When you slice on Franchise, the ranking logic only sees that subset, so the supplier order breaks.
To fix this, you need to rank suppliers globally by spend (ignoring Franchise) while still keeping other slicers active.
Try the bellow measure:
Rank = RANKX (ALL ( Sheet1[SupplierName]), CALCULATE (SUM ( Sheet1[Sum of Spend] ), REMOVEFILTERS ( Sheet1[Franchise Group] )),,DESC, DENSE
If you also want to dynamically show Top N suppliers.
Create a What-If parameter table (TopN[Value] = 1..20).
Add this measure.
Show TopN = IF ( [Rank] <= SELECTEDVALUE ( TopN[Value] ), 1, 0 )
Use Show TopN = 1 as a visual filter on your 100% stacked bar.
This way, your stacked bar will always sort suppliers correctly, even with multiple slicers applied.
Thanks,
Akhil.