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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

@GilbertQ    Great post...thanks for sharing!  

 

How could I add another condition to this?   For example, I am using your example for Top N customers (by shipped revenue value).   

 

Works great in a bar chart with the customer name on it.   However, on the report page I also have a shipped revenue by year, quarter, month and this visual is not being filtered when I use the parameter.   My goal, for example, would be have that chart react whenever I adjust the parameter slider.    So if slid the parameter value to the Top 10 customers, both of the charts would display the corresponding correct values.  

 

 

Top N By Shipped Value =
VAR SelectedTop = SELECTEDVALUE('Top N by Shipped Revenue'[Top N by Shipped Revenue])
RETURN
SWITCH(TRUE(),
    SelectedTop = 0, [Shipped Revenue],
    RANKX (
            ALLSELECTED( ShippedOrdersALL_Query[Bill To Customer]),
            [Shipped Revenue]
                )
                  <= SelectedTop,
        [Shipped Revenue]
)

Hi @Anonymous 

 

You would need to put the measure into your visual if I understand your question.

Anonymous

@GilbertQ   Yes, I do have the measure in both visuals.    

 

So, for visual with the customer names, the measure above works perfectly fine.  I can adjust the parameter to 5 and it shows the top 5 customers...adjust it to 15 and it shows the top 15.  

 

However, I also have a visual below that  - a column chart showing quarters, months.   And I have the measure on that visual as I mentioned above.  When I adjust the parameter, it does not affect this chart...but my goal is that I do indeed want it to correspondingly adjust to the parameter measure.    So if I adjust the parameter to the Top 5, the shipped revenue values in the chart below should adjust accordingly showing the shipped values of those Top 5 customers by whatever date period (quarter or month).  

Hi @Anonymous 

 

Thanks for that I think then you need to create another measure using the same What-If parameter as part of this additional measure.

@GilbertQ - I have followed the same why like you explained. My table is filtering as expected but the totals are not changing accordingly.

 

TopN City = 
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])
RETURN
SWITCH(TRUE(),
    SelectedTop = 0, [Sales Amount],
    RANKX ( 
            ALLSELECTED(  'Orders'[City] ), 
            [Sales Amount]
                )
                  <= SelectedTop,
        [Sales Amount]
)

 

 

However, If I change 

 

ALLSELECTED('Orders'[City])

 

to

 

ALLSELECTED('Orders')

 

Then the totals are adding up correctly by number of rows are reducing (E.g., If I enter 5 in the slicer, I'm only getting 4 rows in the table)

 

Can you please help me with this? Thanks!

Thank you so much

The only downside I found was that the total does include everything and we would like to see the total based on the specific top N, it would be like a subtotal. @GilbertQ any workaround to this? Thanks,

 

@akhilduvvuru did you find an alternative solution to the problem of the totals?

Hi @GilbertQ ,

 

I managed to create the parameter and the table seems to be working.

 

The thing is I would like to visualize it in stacked bar chart. The chart works too but I want the breakdown by their categories. When I put the categories as the legend, it doesn't reflect the parameter (example: top 5, the bar chart shows 10 value). Do you have any solution to it? Thanks!

 

How can I adapt this to toggle between my selection & "All"?

Hi @GilbertQ , thank you for your article. But when we do the percentage contribution and select the number in the slicer, the percentage total is not correct so how can we fix with your formular?

 

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

Hi @blader1989 

 

When working out the percentage you will need to go back to each item that you used to calculate the percentage and work it out from there?

Hi @GilbertQ ,

 

Here is my DAX for the percentage when select the dynamic filter

 

TopN CustomerSalesContribution Matrix = IF (
    COUNTROWS ( VALUES (raw[Customer Name] ) ) = 1,
    [TopN Customer Sales Contribution]
    ,
    DIVIDE(
        [TopN CustomerSales Matrix],
        'TopN'[TopN Customer]
    )
)

But the result is not as my expected so can you guide me how to modify this DAX to appear like the red text in the image below? Like when I choose the TopN is 5, it will divide each customer sale with the total sales of TopN filter.

Capture.png

 

Thank you in advance.