I loaded some data from an Excel table (called FactTable) into a Power Pivot data model. On each row of data there was an Origin value and an Actual Revenue value.
I wanted to calculate an Actual Revenue subtotal for each Origin (similar to SUMIF) and use the subtotal to calculate a Rank for each distinct Origin. I then wanted to use the Rank as a filter in my Power View report so that only the Top 3 Origins based on Actual Revenue were shown in the report.
Below are the formula’s I used – both were added as new columns to the Power Pivot data model.
Subtotal called Actual Revenue by Origin: Calculate(sum(FactTable[Actual Revenue]),ALLEXCEPT(FactTable,FactTable[Origin]))
Rank: RANKX(FactTable,FactTable[Actual Revenue by Origin],,,Dense)
The Dense parameter at the end of the Rank formula was important as it determines how ties should be handled. I used it to return a clean 1, 2, 3, 4, 5 Rank as opposed to the alternative which would have looked something like 1, 234, 456 etc.
The alternative would have taken into account the number of rows that existed for each Origin. So if there was 233 rows of data for the Origin with the largest Revenue subtotal then the Origin with the second highest Revenue subtotal would have shown a Rank of 234 not 2.
Update: The RANKX function will rank values in descending order by default (so the largest value is rank number 1). The ascending/descending order can be controlled by placing a 1 or a 0 before the ,Dense parameter. 1=ascending and 0=descending.
e.g. RANKX(FactTable,FactTable[Actual Revenue by Origin],,1,Dense)
It would be more “normal” to place the RANKX calculation in a Dimension table (where each Dimensional Attribute is only listed once) rather than in the Fact table where each Dimensional Attribute may be listed many times.