Year: 2014

Ranking within Slicer Selection in Power Pivot

Ranking within Slicer Selection in Power Pivot

May 21, 2014

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.

Ranking within Slicer Selection in Power Pivot

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

Data Model

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


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.

Pivot table showing City, License Count and Rank

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

Adding slicer to pivot table

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

Filtering by City doesn't reset Rank

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.

Add 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.

Filtering on new slicer changes the rank now!

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.


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!

Posted by SQLJason, 1 comment
Download link for my 24 HoP Session

Download link for my 24 HoP Session

February 12, 2014

It’s been almost 3 months since I blogged and this has been the longest time I have been away from this space since I started blogging. There are a couple of reasons behind it (which included the suspension of my blog by Google for allegedly hosting malicious code!!!) but more on this later. For now I just wanted to post the download link for the demos used in my 24 Hours of PASS session – DataViz You Thought You Could NOT Do with SSRS. image The report solution files are available for download here. The rdl files for the 8 reports that I showed as well as the shared data sources are present in the zip file. You can run the reports by pointing the shared data sources to your local database for most reports. However, the following reports will not run as additional information or data is required – Squarified Tree Map.rdl and MCFC.rdl. However, you can learn more about these two charts by visiting my earlier blogs on Heat Maps for SSRS using Map Control and Linking and Brushing Visualization with SSRS. This was a sneak peak of my session that I am presenting at the PASS BA Conference. For people who are wondering what this is all about – On February 5, BA and BI community experts presented a series of 1-hour webcasts delivering best practices and expert tips for getting the most from your data. This 24 Hours of PASS event provided a sneak peek at what you can expect at the PASS Business Analytics Conference May 7-9 in San Jose, CA. Sign up today and you can use my discount code BASF2A to save $150 off the registration price. The 24 Hours of PASS session recordings (which includes my session also) are now available for streaming. Access the recordings for free now.

Posted by SQLJason, 4 comments