Ranking within Slicer Selection in Power Pivot

Share this...

Twitter

Linkedin

Facebook

Pinterest

Reddit

I know it’s been ages since I posted something, and I do have lots of lame excuses so maybe it deserves a post on it’s own. But for now, I want to jot down a workaround on how to rank a column based on the slicer selections for the same column.

The other day, I was working on an excel dashboard and I got a requirement to rank a field while the field values are there on the slicer. While I am used to getting requests on ranking a field based on a measure, this was the first time someone was asking me to rank a field based on a measure while the column was on the slicer. The user just wanted to rank within the selected values of the field in the slicer and not as a whole. To explain this, I just made a very simple example with the model given below

The FactLicense table contains data on new liquor licenses and I made a simple measure License Count to count the number of licenses. The FactLicense table is related to the Geo table through the Zip Code column. The requirements were that

• the City field should be ranked by License Count
• there should be a slicer displaying the values for City
• the ranks should update based on the Slicer selections

Solution

1) To solve the first part of the requirement, I made a simple ranking measure by City as shown below.

LC Ranked by City:=RANKX(ALL(Geo[City]), [License Count])

You can see the results in the pivot table below.

2) For the second requirement, we can just add a slicer for the City field and then connect it to the Pivot Table.

3) Now the third requirement is the interesting one. When I try to filter by some cities, I get the result below.

You can see that the Ranks don’t get updated based on the slicer selections. The user wants the ranks to start from 1 but you can see that the ranks are still based on the entire city list and not just the selected city list. Now it is impossible (atleast as far as I know) to reset the ranking if the same field is on the rows as well as the slicer. The reason is that the Rank measure operates on ALL(Geo[City]) and hence removes any filter or selection that is already made. So what do we do?

4) A simple workaround is to make a calculated column (say City Slicer) and make the Slicer based on this new calculated column.

Make sure to remove the old slicer as well as connect this new slicer to the pivot table.

5) Now when we select the City in the slicer, you can see that the Ranks get reset.

You can further rename the Slicer such that it says only City and you can hide this calculated column so that it is not visible to the other users analyzing the model. This will help avoiding confusion among the end users (Eg – why do we have 2 city fields?) and at the same time, you can make your Excel dashboard with the fulfilled requirements.

Update

Within seconds of posting, Miguel Escobar (twitter) comes up with a much better way of doing this using the ALLSELECTED().

LC Ranked by City1:=RANKX(ALLSELECTED(Geo[City]), [License Count])

This would be the ideal way to do it as we don’t have to create a new calculated column and still achieve the same results. Thanks a lot for this Miguel!