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.
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 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)
Now our chart will include Our Company no matter its Rank.