Showing the top N values of a dimension, where you can use a slicer to vary N
Following a question from a Power BI course, this blog shows how you can get a page to show the top N customers by sales value, where N is a number controlled by a slicer.

Posted by Andy Brown on 06 December 2018

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using a slicer to show the top N companies by sales, etc

Someone asked me on a course yesterday whether you can achieve this:

Top N sales

The idea is that you should be able to change the number using the slicer at the top of the report, and all of the visuals on the page should then only show data for the top N customers by sales (or in my case, the top N towns by quantity).

I have found a way, although as is always the case I don't know if it's the only way, or even the best one.  Here goes!

This blog is aimed at people with a reasonable existing knowledge of Power BI - newbies beware!

Step 1 - Create a what-if parameter

Start by creating a what-if parameter:

What-if parameter

Click on this icon to create a what-if parameter.

 

I've set mine to go from showing the top 1 town to the top 10 towns:

What-if parameter dialog box

The values for our parameter.

This gives me my slider:

Numeric slicer

I've added a title and some formatting, and hidden the slicer header.

 

Note that if you change your mind about the highest value at any point, you can just change the parameters to the GenerateSeries function created:

The threshold table

To do this you should first click on the table as shown.

 

Here's how to edit the DAX formula for the table:

Generate series function

The numbers in the formula give the minimum and maximum rankings, and also the step value when you use the slicer.  You can change these numbers to any you like!

Step 2 - generate new columns in the dimension table 

Now go to your table containing the things you want to rank by (towns, customers, whatever) and create 3 new columns:

3 new columns

See below for what to put for each of the last 3 columns.

The [Sales] column above should show the total sales for each town, so that you can then rank them:

Sales = CALCULATE(SUM(Purchase[Quantity]))

Note that you need the CALCULATE function because you need to create filter context for each town, to avoid just summing across the entire table.

The [Sales rank] column should show the position of each town in order:

Sales rank = RANKX(Town,[Sales],[Sales],DESC,Skip)

This ranks the towns by their sales in descending order. 

Finally, the [IfInclude] column should say whether each town could possibly be included:

IfInclude = IF(Town[Sales rank] > 10,FALSE(),TRUE())

You'll see in a bit why we need this!

Step 3 - Relating the parameter and dimension tables 

We want the choices made for your parameter to change which towns are displayed, so create a relationship between (in our case) the Town and Threshold tables: 

Relationship

Drag the threshold value onto the [Sales rank] column.

This creates the relationship between the two tables:

Relationship created

The relationship created.

Step 4 - Excluding unwanted towns 

The problem remaining is that the slicer doesn't work!

Slicer not working

No matter what number you select in the slicer at the top of the page, you still see all of your towns.

 

The reason for this is that Power BI doesn't create inner joins between tables, but outer joins.  When you select to show the top 3 towns by sales in the slicer, the filter context for the Threshold table looks like this: 

First 3 numbers

The values selected by the slicer shown above.

 

The relationship between the two tables will mean that any visual will show all of the towns where the sales rank is 1, 2 or 3, but also (by default) all towns where there is no match found:

Outer join link

This is not an inner join, so you'll see towns which have no matching thresholds.

 

My solution to this was to create a page (or report) filter to exclude these "blank" matches:

Exclude most towns

This is what the IfInclude calculated column was for - only show towns where the value of this is True (ie which are in the top 10 by sales).

 

Step 5 - creating the card title

It's not really part of this blog, but just in case it's left you scratching your head the following title was created as a card visual:

Card title

To show this create a card, and use it to show the measure shown below.

The card is displaying a measure with this formula:

Chosen =

"Showing data for the " &

MAXX(VALUES(Town[Sales rank]),[Sales rank]) &

" towns by sales"

Again, I don't claim that this is the only way to do this, or even the best.  If you're not that familiar with DAX you may prefer to miss this bit of the blog out.

The result - a variable top N 

The result is very nearly perfect:

The result

It would be nice not to have to hard-code in the maximum top N value that you could ever want, but I can't see any way round this.

Hope this works for you! 

This blog has 0 threads Add post