MIP Logo

How to Show Top and Bottom N Value in a Single Visual in Power BI

This post was originally published on The Data School blog between 2018 and July 2025, before our program was renamed to MIP’s Analytics Career Accelerator. References throughout this article to “The Data School” or “DS” all refer to what is now MIP’s Analytics Career Accelerator. The program, its people, and its commitment to launching outstanding analytics careers remain the same – just under a new name.

If you are having trouble viewing this article, please report it here. 

We are aware that in Tableau, we have the ability to utilize parameters for controlling the display of Top and Bottom N values in a single visual. However, you may wonder if the same functionality exists in Power BI. The answer is Yes! In this blog, I will show the necessary steps to achieve this.

 

1. Create a new table which will be used as a slicer then

2. Create a measure you want to show, in my case, it would be: Average Gini = AVERAGE(Sheet1[GINI])

3. Then, create the following measures

TopX = SELECTEDVALUE(Slicer[Value])  (we are taking the values which we created for slicer selection.)
TopRank = RANKX(ALL(Sheet1[Country]),CALCULATE([Average Gini]),,DESC) (Sort the countries in descending order)
BottomRank = RANKX(ALL(Sheet1[Country]),CALCULATE([Average Gini]),,ASC) (Sort the countries in ascending order)
Result =SWITCH(TRUE(),[TopRank]<=[TopX],1,[BottomRank]<=[TopX],-1,blank())
If the value of TopRank is equal to or less than the value of TopX, the result will be 1. Similarly, if the value of BottomRank is equal to or less than the value of TopX, the result will be -1. Otherwise, if neither of these conditions is met, the result will be left blank.

4. Then, drag the value we created before in the slicer table to slicer.

 5. Choose the bar type and drag country to y-axis, average gini to x-axis. And drag Result to filter, select not blank and apply filter.
Then you will have your chart.

Share this post