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 3^rd, then the next organization will be ranked 5^th (skipping 4^th).

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.

The filter is a shown below.

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 11^th 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)

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