Ranking is a common but tricky function in Power BI, especially when it comes to “dynamic ranking”.
Requirement:
Rank resellers in a table based on their total sales. The resellers are ranked dynamically within different countries selected.
Solution:
First, let’s look at how to set up the data model:
Since it’s dynamic, so we need a measure for it, instead of a column. Now let’s create two measures:
Base measure:
Total Sales = SUM( Sales[Total Sales] )
Ranking measure:
Total Sales Ranking = IF( ISINSCOPE( Reseller[Reseller Name] ), VAR TotalSalesCurrentReseller = [Total Sales] VAR ResellerRank = RANKX( ALLSELECTED( Reseller ), [Total Sales] ) VAR Result = IF( TotalSalesCurrentReseller <> 0, ResellerRank ) RETURN Result )
Throw the second measure along with the Reseller Name, Total Sales in a table. You have the expected result:
