Dynamic Ranking

[SagarVasaikar]

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. I want the visual to sort based on the ranking of the total spend for suppliers irrespective of the Franchise groups.

Attached screenshot 1 shows when no slicer is selected. It works as expected. The table visual is for reference to check ranks.

When I select a slicer value from franchise group ranking breaks.
As shown in screenshot 2.

Also, in my actual data I have 10 slicers, so when I slice down using multiple slicers the ranking breaks.
Kindly help me solve the problem.

Measure used is as below
Rank = RANKX(ALLSELECTED(Sheet1[SupplierName],Sheet1[Franchise Group]), CALCULATE(SUM(Sheet1[Sum of Spend]), ALLEXCEPT(Sheet1,Sheet1[SupplierName],Sheet1[Owner],Sheet1[Year])), ,Desc,Dense)

[FBergamaschi]

Can you please shar ethe pbix?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[v-agajavelly · Accepted solution]

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.

[v-agajavelly]

Hi @SagarVasaikar ,

Just checking in to see if the Rank measure and TopN setup worked for your stacked bar. Were you able to get the suppliers sorting correctly even when applying multiple slicers?

Would love to know if this solved the issue or if you ran into any other quirks.

Thanks,
Akhil.

[v-agajavelly]

Hi @SagarVasaikar ,

Circling back, did the Rank measure and TopN approach fix the supplier order for you? If it’s still not behaving as expected with slicers applied, could you share an example of the output you’re seeing so we can troubleshoot further?

Thanks,
Akhil.

[v-agajavelly]

Hi @SagarVasaikar ,

I hope the response provided helped in resolving the issue. If you still have any questions, please let us know we are happy to address.

Thanks,
Akhil.