Forum Discussion

SagarVasaikar's avatar
SagarVasaikar
New Member
11 months ago
Solved

Dynamic Ranking

I want to create 100% stacked bar chart visual in power bi. Where it should have supplier name, franchise name in legend, total spend. I want to dynamically sort based on the top n selected in slicer...
  • Anonymous's avatar
    Anonymous
    11 months ago

    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.