Power Pivot subtotal and ranking using SUM, ALLEXCEPT and RANKX

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.

We hope you found this useful and/or interesting. If so please like, share or comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s