We have two tables, Sales and Product:
Loading the data into Power BI, the two tables are related by ProductID.
Now create a DAX Measure:
Rank ProductID By Units = RANKX('Product',sum(Sales[Units]))
Here’s a table with the new measure and ProductID. But notice that the Rank for all rows is 1. This is because of the query context. The context is the current ProductID, so since the context only includes the the product ID in the current row, the rank is always 1.
So that didn’t work! Here are two ways that do work:
Rank = rankx(ALL(Sales[ ProductID]),calculate(sum(Sales[Units])))
Why use calculate? In order to transition from row context to filter context. Another way to get filter context is to use a measure:
Sum of Units = sum(Sales[Units])
Rank using Measure = RANKX(ALL(Sales[ProductID]),[Sum of Units])
ALL(Sales[ProductID]) removes the ProductID from the context so that the rank can span all four or our ProductIDs. Ranking is done on the Sum of Units meaure.