Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

GilbertQ

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
Anonymous

Hi @GilbertQ,

Amazing Article !!!
I want to ask if you figured out a way to use legends along with this.

I need to show sales by Supplier (Top N) and use legend for country(or any other field). But the things are breaking and every legend is being shown as an individual row.
Thanks in advance

Anonymous

Most excellent

Helper II

@GilbertQ 

Hi - I used your brilliant suggestion for top N and it worked great.  However, I am trying to do the same thing for BOTTOM N and I cannot get it to work quite right

 

selected value set at 3 - only shows first 2 of bottom nselected value set at 3 - only shows first 2 of bottom n

 

For Bottom N:  since the above automatically seems to sort from largest to smallest - I would need to have the formula sort from smallest to largest.  I tried adding ASC in the Allselected section of formula:

Bottom N Baseline = GENERATESERIES(0, 60, 1)

Bottom N Baseline selectedValue = SELECTEDVALUE('Bottom N Baseline'[Bottom N Baseline])

Total Baseline Bottom N (Commodities) =

VAR SelectedBottom = SELECTEDVALUE('Bottom N Baseline'[Bottom N Baseline])

RETURN

SWITCH(TRUE(),

SelectedBottom = 0, [Total Baseline],

RANKX (

ALLSELECTED( 'Baseline'[Commodity] ), [Total Baseline],,ASC, Dense)

<= Selectedbottom, [Total Baseline])

However, that did not work quite right.  It appears to, when only have slicer for Bottom N - set for say 3 - it shows the bottom 3 commodities in the visual.  However, when I slice by category - 1 of the categories does show bottom 3, but the others show the bottom 2 only.  In a table view - there seems to be more than 3 valuse returning for this Bottom N Baseline.

 

 

How can I modify the DAX so that I get Bottom N with the fuctionality of the Bottom N slicer and still use all my other filters?

Hi @GilbertQ 

 

can you submit the (Pbix) file please?

How do you work this if there is another column, regardless of hierarchy (whether parent, same level or child).  For example Suburb or Region.  How do you ignore the slit up in rankings with another column and still only drive by City rankings only.

https://community.powerbi.com/t5/Desktop/matrix-top-n-rankings-over-one-column-with-other-columns-pr...

Now posted as per above to the pbi community...

Finally figured out a solution, see community post link above.

Hi @garythomann_vac 

 

It would depend on the columns and how it integrates with your data.


I would suggest giving it a try and see what the outcome is?

I already have a "priority" column for different multiple organizations. So each organization has its own ranking.IF management wants to look at the top 3 or 5 for all orgs, I need to be able to show that in the table. I'm having an awful time trying to get a measure together that works off of a whatif parameter. Do you have any recomendations?

Hi @tuck1 

 

What I would do is to possibly create a new measure with a new WhatIf parameter to work for this example.

Hi @GilbertQ I used your method and it gets me half way through what I'm trying to do. I have this page in my report:

KMcCarthy9_1-1636570061210.png

and I would like to hopefully expand on your method to be able to eliminate all the different buttons for Top/Bottom N. 

 Ultimately I would like them to choose their KPI from a slicer and choose the Top N number from a slicer like what you have created. something like this:

KMcCarthy9_0-1636569998257.png

 

Is this possible?

Thanks! 

 

Hi @KMcCarthy9 

 

This might be possible if you are able to get the details from the selection and then pass that through to the Slicer?

@GilbertQ 
Thanks for this post. It would be great if you could share how Dynamic BottomN can be achieved in the same manner. If possible kindly attach the pbix file of Dynamic BottomN .
Thanks in advance.

Hello everyone! I got stuck in solving a query. Please suggesy any solution:
My objective is to find daily summation of list (containing zeroes & non-zeroes, 8 entries for a day) excluding first 2 occurence of non-zeroes in the list.
DESIRED SUM: 5th Feb (12+0+15); 6th Feb (5+16+0+15)

Date         List 1

05/02/22  0

05/02/22  16

05/02/22  0

05/02/22  0

05/02/22  7

05/02/22  12
05/02/22  0
05/02/22  15

06/02/22  21

06/02/22  0

06/02/22  0

06/02/22  13

06/02/22  5

06/02/22  16
06/02/22  0
06/02/22  15
Need help, tips and tricks
SELECTEDVALUE in calculation not working 
@GilbertQ @Greg_Deckler 

Hi @Mahendran_C_S 

 

What you could possibly do is to change the 

 

<= SelectedTop,

To

>= SelectedTop,

 

@GilbertQ 

Hello,

Great tuto!!  Many thanks!  How do you calculate the TopN Gross Profit in %?  I tried your formula, it works for TO, Quantity but not for GP%.

May you please help?

Thanks, and best regards

Rachel