RANKX in DAX: How to include Your Company even when they are not in top 5

My customer wanted to see the top 5 providers of a service in a Power BI Report. I added a RANKX calculation to the model.

RANK =rankx(all(Sales[Organization Name]),sum(Sales[Volume]),,DESC,Skip)

 

Here any filtering is removed to get all sales Organizations, then the sum of the Volume is used to rank the Organizations in descending order.  The Skip will skip ranks on ties.  So if two Organizations are ranked 3rd, then the next organization will be ranked 5th (skipping 4th).

Organization Rank Sum of Volume
Org A 1 10,000,000
Org B 2 9,000,000
Org C 3 8,000,000
Org D 3 8,000,000
Org E 5 6,000,000
Org F 6 5,000,000
Org G 7 4,000,000
Org H 8 3,000,000
Org I 9 2,000,000
Org J 10 1,000,000
Org K 11 500,000
Org X 12 200,000

 

In Power BI we can add a filter to the display to show just the top 5 organizations.

top5

The filter is a shown below.

filterrankx

Including Our Company

My customer liked this but they had one more request.  The wanted to always see our company so they could compare.  Our company is Org X.  In the example we are ranked 11th in this market.  So how do you include us?

I came up with the following solution.

RankwithOrgX:=SWITCH(FIRSTNONBLANK(Sales[Organization Name],sum(Sales[Volume])),”Org X”,0,[Rank])

I use the FIRSTNONBLANK function to find our organization “Org X”.   Using the switch function, I place a zero value for our Organization.  For all other Organizations I use the value from the Rank calculation.

Organization Rank RankwithOrgX Volume
Org A 1 1 10,000,000
Org B 2 2 9,000,000
Org C 3 3 8,000,000
Org D 3 3 8,000,000
Org E 5 5 6,000,000
Org F 6 6 5,000,000
Org G 7 7 4,000,000
Org H 8 8 3,000,000
Org I 9 9 2,000,000
Org J 10 10 1,000,000
Org K 11 11 500,000
Org X 12 0 200,000

Org X shows as 0 under RankwithOrgX.

Now if we filter on RankwithOrgX less than or equal to 5. (remove our previous Rank filter)

filterwithorgx

Now our chart will include Our Company no matter its Rank.

Top5withOrgX

 

 

Advertisements