cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic TopN made easy with What If Parameter

In this post I am going to demonstrate how to create a Dynamic TopN slicer using a What-If Parameter. This will allow your user to simply use the Slicer/Slider to view the TopN values and as a bonus if the user slides it to zero, it will display everything! Who doesn't like something that is easy to create, but makes it so much easier for the user to gain insights into their own data easily and quickly?

 

Example

For this example, I am going to be using a [Sales Amount] measure from my Orders table. Next for the TopN, I am going to be looking for the TopN by City. This is a key component when identifying what you want your TopN to be based on. NOTE: If the explanation of the TopN Measure can be a bit complex, you can either copy the code and modify it for your requirements or you can view the animated GIF further below so you can see it working. 

 

Creating the TopN Slicer

  • In Power BI Desktop, I went to the Modeling tab and then clicked on New Parameter
  • I then gave it the following properties as shown below.
  • One thing to NOTE is that I set the Minimum to 0 (zero)
  • When you are creating this TopN What-if parameter, you can change anything for the Maximum, Increment and Default.
  • I could then see the table created on the right-hand side.
  • As well as the Slicer on my reporting canvas, which I formatted

Creating the TopN Measure

  • Below is the TopN measure, which I will go into detail how it works below.
TopN City = 
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])
RETURN
SWITCH(TRUE(),
    SelectedTop = 0, [Sales Amount],
    RANKX ( 
            ALLSELECTED(  'Orders'[City] ), 
            [Sales Amount]
                )
                  <= SelectedTop,
        [Sales Amount]
)

 

  • Line 2, is where I created the Only Variable called SelectedTop
    • This is getting the selected value from the Slicer.
    • If it is slid to 5, this variable SelectedTop will store 5.
  • Line 4 is where I used the SWITCH(TRUE()
    • What this does, is it enables me to pass multiple statements to evaluate in one DAX function.
    • NOTE: You could possibly do this with an IF statement, but I prefer doing it this way, because quite often the requirement changes to have more than 2 conditions, so doing in this way it is easy for me to add another condition.
  • Line 5 is my first condition, where I have said if the TopN Slicer (SelectedTop)= 0 (zero) then display all the [Sales Amount]
    • It will do this because there is no filter context being applied on the [Sales Amount]
  • Lines 6 – 9 is where the Magic happens and uses the values from the TopN slicer.
    • This is also the second condition for the SWITCH(TRUE() DAX expression.
    • Even though this is for TopN values, I use the RANKX to achieve the desired result from Line 6
    • Next, I am using the ALLSELECTED, because in my table I want to select the TopN for the City values.
    • Line 7 is where I am selecting from my table Orders and the column called City ALLSELECTED( 'Orders'[City] ),
    • Line 8, is where I am specifying my measure for the RANKX which is the [Sales Amount]
    • Line 9, is where I am closing off the RANKX function.
  • Line 10 is where I am now comparing it to be less than equal to the selected slicer value SelectedTop
    • If this evaluates to TRUE, then display the RANKX up to and including the selected slicer value.
  • Line 11 is the ELSE condition for the SWITCH(TRUE()
  • Line 12 is closing off the SWITCH DAX Function

TopN Slicer in Action

Please watch the following animation below in which I will demonstrate by using the Slider I can to from Top 10, to Top 5. And then by sliding it to 0 (zero) it will show all the cities.  

                             

Conclusion

As I have shown, by using the What-If Parameter and some DAX there is now a way to easily create a dynamic TopN that is easy for your users to use within their reports. As always, if there are any questions or comments, please leave them in the section below.
Comments

This is great! and amazingly arrived just in time for me to impement in something I happened to be working through this week to create a dynamic slicer like this.

 

What's the best way to just show the sum of the TopN cities in your example? I'd love to show the TopN cities, their total, and how much of the total not filtered value the topN is.

hi @anktaggrwl

 

To show the total with the TopN, you could just take the measure and put that in a card or any visual.

 

With regards to the values not filtered you could possibly do it by getting the entire total and then subtracting it from the TopN total, which would then give you the difference?

Maybe I'm missing something, but even in your gif above, when you change the TopN slider, while the list of cities adjusts to show the top 5, 10, 20 cities and their amounts individually, the sum in your table still calculates and produces a total based on the full list of cities regardless of the parameter value. 

 

The same therefore is happening in a card visual. 

Anonymous

Hi Guavaq,

Is there any way you can email me the sample I've try to do that but it dosent seems to recognized the column I want to filter on - where do you please the measure on the TopN table or on the Order table?


Thanks

Oded Dror

 

 

Hi!  I have a very large matrix that has been built, and the dynamic TopN slicer will only filter based on the value (in my scenario it is this year revenue and SKU), but will not work with the other values in the matrix.  Is there a way to do the TopN based on SKU and revenue, for example, but filter across the whole table?  Thanks!

Hi @anktaggrwl

 

You are indeed correct, what I need to do is update this blog post with the measure below which will then display the right values in the total

 

TopN City Matrix = 
IF (
    COUNTROWS ( VALUES ( 'Orders'[City] ) ) = 1,
    [TopN City]
    ,
    SUMX (
        VALUES ( 'Orders'[City] ),
        [TopN City]
    )
)

If you put that into the matrix it will then work as expected.

Hi @Anonymous

 

Here is a link to where you can download the sample data.

 

Dynamic TopN made easy with What If Parameter.pbix

 

If you still get stuck please let me know.

Hi @shockian

 

Would you be able to provide some sample data, as this is often a lot easier for it to work out how to get it working for you.

Anonymous

guavaq,

 

Thank you, one more question the total per TopN showing the Grand Total and not what the Top N is

For example if I have 100 rows with total sales of 1,000 and I pick top 5 it should show what the top 5 are

But it showed 1,000

 

Thanks,

Oded Dror

Hi @Anonymous

 

It would depend on the value of the measures

Anonymous

Guavaq,

 

Thank you for the quick responed. I made it work based on your sample.

 

Thanks

Oded Dror

Here is a full blown tutorial on a similar topic with slicers, table filtering and dynamic column selection:

 

https://powerbi.tips/2018/02/crazy-table-gymnastics-part-1-dynamic-column-categories/

https://powerbi.tips/2018/02/crazy-table-gymnastics-part-2-build-support-materials/

 

Nice job 

Guavaq

Hi GilbertQ, Thank you very much for the brilliant post. It working flawlessly in a matrix visual, but I also have graph which shows the customer revenue over month, when I use this method, it is showing topN customers in each month, is there is way I can change it to show the top N customers with respect to grand total? Thanks

Hi @ONCKdata

 

When you say you want to change it to show the TopN Customers for the grand total, could you possibly elaborate on that?

 

I am trying to understand, because the way I see it, the grand total is always the total of all the values selected?

Hi GilbertQ, Thank you for the reply, Please see the image in below link: https://www.dropbox.com/s/j4qbqhai80dy30d/TopN.PNG?dl=0 When I select top 3, I am getting the result for top 3 in a month. Kindly let me know if my understanding is wrong. Thank You.
Hi Gilbert, I have added an graphical representation of of desired and actual output, please find the below link. https://www.dropbox.com/s/r7thwmzbcbd6lko/Dynamic%20TopN%20made%20easy%20with%20What%20If%20Paramete... Looking forward to your help. Thanks.

Hi GilbertQ,

In addendum to the below query, my matrix doesn't filter to show only those cities (in my case clients). It does show the calculated top 10 amount in the grand total, but also shows other clients showing their values as blanks.

 

Hi @@anktaggrw

 

You are indeed correct, what I need to do is update this blog post with the measure below which will then display the right values in the total

 

TopN City Matrix = 
IF (
    COUNTROWS ( VALUES ( 'Orders'[City] ) ) = 1,
    [TopN City]
    ,
    SUMX (
        VALUES ( 'Orders'[City] ),
        [TopN City]
    )
)

If you put that into the matrix it will then work as expected.

Hi @yash14vashistha

 

If you could please provide more details.

 

From my example above it is showing only the TopN for my Sales Amount Measure.

 

I would suggest ensuring that your measure that you are using is correct?

GilbertQ

 

I fixed that, turned out I had other measures and I had to build a similar calculation for them. Now I am in another fix.

 

I have another matrix where I am applying a visual level filter. This above calculation is not, I think, recognising that filter and giving me blanks because it is rankin the total fees irrespective of any visual level filter. My guess is that ALLSELECTED has to something with it. Can you tell me what I could do in this case? Maybe use a different function than ALLSELECTED?

Hi there you could try the ALLEXCEPT which will change the filter context to select everything except what you select 

Okay. Fixed that.

 

Now, there is this. This function is calculating the sum for even the Percentages and rates. I want the overall average, but when I use AVERAGEX, the total is not correct.

 

TopN City Matrix = 
IF (
    COUNTROWS ( VALUES ( 'Orders'[City] ) ) = 1,
    [TopN City]
    ,
    SUMX (
        VALUES ( 'Orders'[City] ),
        [TopN City]
    )
)

 

 

 

Hi @yash14vashistha I would suggest you post this to the Power BI Community with some sample data where myself or someone else could provide you the solution.

Well, I kind of fixed that. by not using the SUMX. So now I am using something like

TopN City Matrix = 
IF (
    COUNTROWS ( VALUES ( 'Orders'[City] ) ) = 1,
    [TopN City]
    ,
        [TopN City]
    )

 this. So I am not concerend about this right now.

 

I have something else!! I'm sorry for coming back to you again and again. The new problem is that on the matrix with the visual level filter, the calucation takes about a minute to filter and show the results, which is pretty slow!

I just measured it and it took about 2 min 25 sec to filter top15 results

Hi 

@GilbertQ @ONCKdata

Suppose I rank the customers by Total Sales and it works fine. But when I use Month as legend in a bar chart or a column in a matrix instead of showing the individual sales breakdown its somehow showing wrong values. 

Hi Gilbert,

This is amazing . I have a requirement of showing a slicers which have Top N and Button N  . When I click on Top N all my charts should show Top N values and when I click on  Bottom N values all charts should change to Bottom N values.

 

Any help will highly appreciated.

Thank You

I am unable to do this task, as the slicer is not being syncronised with the data in the table.

do we have to make relationship between our main table and top N table that is made ? please guide me 

Hi @Abdul_Aleem

 

You should not need to create a relationship.

 

If you can confirm that you are using the Slicer from the What-If table and not your main table?

Hi @GilbertQ , can you suggest a way to use the TopN on measure 1 (Sales Amount for example) but to include another measure (example Profit) in the table or display? 

 

That means, I want a table which displays City, Sales Amount and Profit. And the Top N applied based on Sales Amount. 

 

Appreciate your help. 

Hi there

 

You should be able to add in the Profit measure into your existing table.


That would work because the TopN is being applied to the Sales Amount, and this would filter the data based on the Sales Amount and show the relevant Profit?

Nei Visitor

Hello @GilbertQ ,

i'm just new in BI and learned it from watching videos. I am currently doing visualizations to show dynamic graphs and needs help to link. first matrix shows the branches of the pharmacy showing sales and growth. 2nd, when u click a particular branch it will show u the sales per product range. Lastly, when product range is click, 3rd matrix will show the top 10 sku of the selected range for a particular branch.

 

Currently what happening is when i click a particular branch in matrix 1, it will show the sales per product range in my matrix 2. But when i tried to click a particular range it table/matrix 2, yes it showing the top 10 sku in my matrix 3 but not anymore connected to matrix 1. Will u be able to help me on this.

 

appreciate much. Thank u

Hi @Nei 

 

Have you tried to do the CRTL + Click when moving across the matrix, that should keep the existing selections?

Nei Visitor

Hi @GilbertQ ,

 

Awesome.: It's working fine now.

but may i ask your expertise  on the following as well

 1. how to arrange chronological the months in my slicer? i tried to go to formatting but its diabled.( i have 1 appended table for the pharmacy and separte table for dates. )

2. Why the data is not showing in my matrix table and my file source are complete.

3. for the meaures Ranking to show top 10 pharmacy branches, why ranking apeears multiple 1 where their values are different ( i used Rankx( ALL

 

Thanky you and appreciate it so much.

Hi @Nei 

 

Glad that it is working

 

1. You can use the Sort By column in order to sort your months in the right order.

2. It is possible that something is either being filtered out in the Power Query Editor, or a filter on your Power BI Report?

3. It will be because the ranking is defined for a particular column on a table. If you need it for another column you will need to create another measure.

Nei Visitor

hi @GilbertQ 

1. i tried the sort  by column in order but its not working. Smiley Sad

2. i found the mistake. thank u Smiley Happy

3. i ranked it using the column units, where my pharmacy needs to be ranked according to total units sold and i dont know why it's giving the same ranking, total sales are different from each other.

 

 

in addition, i was doing a chart to show my top 10 using the visual fileter advance fileter, top 10. it's working but how to i want to show the sales of the full customers and not only 10.

 

Thank you.