How to filter your chart with a Top N Numeric Parameter

 

Hi everyone! In today’s blog I’ll show you how to add a Numeric Range Parameter to filter Top N Values in your charts. We will need to create a few calculations in order to achieve this.

For this Bar chart I want to be able to Filter the number of beers shown using a Numeric Parameter.

My Bar Chart shows Name of beers and their price colored by Type of Beer. These are also ordered by descending Average Rating.

Create Measures

The first thing I need to create is a “Numeric Range” Parameter under Modeling> New Parameter. Set up the values as you need.

 

 

Create a New Measure which you will then use to Rank your Beers.  In my case I needed the Average Ratings for all the beers.

 

 

Now you can RANK your beers by this new measure created. The next DAX Expression Ranks ALL the beers [name] by the measure we just created, ‘AA Ratings Average’.

We need to create one last calculation that will compare IF my RANKX beer Names are within my TOP N Parameter. If my beer is within my TOP N I will assign a value of 1, else 0.

As we can see in the table below, only the top 3 beers are assigned a value of 1.

To allow the TOP N Beers parameter to filter our chart we will need to configure “Beer Rank Within Selected N” in the filters pane. Select “Show items when the value” is 1 and Apply.

Now only my Top 3 beers by Average Rating are shown in my chart.

Format your chart

Swap your table to your chosen chart type and Format the visual as you like.  In my case I needed a horizontal bar chart and I added to the legends the “Type of Beer” so I will see the Top N of every Type ordered by its Average Rating. The pie chart above filters the bar chart so if we need to only “Stouts” I can click on it and the bar chart will show me the top N of that “Type of Beer” only.

 

Let me know in the comments if you find it useful. If it works, crack a beer and celebrate your learning!