Dynamic TopN reports in Power BI

[Anonymous]

There’s no doubt PowerBI is a powerful reporting tool.  The method below outlines a pattern that can be used to meet the needs of several end users.  In order to create stunning Power BI reports that can allow a user to really dive into the data, you need to know DAX.  DAX will take your Power BI report to the next level, and the trick I’ll outline below is how to create a Dynamic TopN report.  It’s one thing to look at a KPI such as Sales company wide, but what if you want to identify the stores or items that are driving that KPI?

 https://community.powerbi.com/t5/Data-Stories-Gallery/Retail-Analysis-Dynamic-Measure-amp-TopN-Selection/m-p/121080

View the report here: https://community.powerbi.com/t5/Data-Stories-Gallery/Retail-Analysis-Dynamic-Measure-amp-TopN-Selec...

TopN reports are where the users can choose to see the performance of the Top 5, Top 10, Top N… properties that contribute to a KPI.  The first step is to create a table that is disconnected from your data model (no relationships with any other tables).  This table, named “TopN” will look like below:

Top

5

10

15

20

30

50

This table and field will be used as a Slicer in the report, and the users selection will be captured using the following measure:

SelectedTopNNumber = Min('TopN'[Top])

This will return 5 by default, but you may want the default value to be something else.  In this case, you can write the following measure:

SelectedTopNNumber = IF(HASONEVALUE('TopN'[Top]),Min('TopN'[Top]),10)

The HASONEVALUE checks if the user has selected a value or not.  If they have, then use the number they’ve selected, otherwise set the default value to 10.

Let’s say we’re looking to see the TopN contribution for Store’s.  We’ll use the following measure to determine the store’s rank.

Store Rank By Selected Measure = IF(HASONEVALUE(DimStore[StoreName]),RANKX(ALLSELECTED(DimStore),[Selected Measure Value],,0))

You can replace “Selected Measure Value” with the name of the measure you want to rank the store by.  We use ALLSELECTED() because we want the ranking to only be within the store’s that the user has selected.  For example, if the company has store’s in all 50 states, but the user has selected Wisconsin in a slicer, we want the measure to rank each store within just Wisconsin, not return the overall company ranking.

Next, we need to determine if a given store should be included or not.  So we’ll add another measure:

Should Store Be Included = IF([Store Rank By Selected Measure]<=[SelectedTopNNumber],1,0)

Now, we can build a table on a Power BI report with the following fields and filters:

 

And after adding TopN[Top] as a slicer, combined with some formatting, the end result is a report where the user can choose their own TopN.

If you have any questions for me, you can reach me via LinkedIn or in the PowerBI Community.

I'm working on some blogs that outline some techniques for the Retail Analysis -Dynamic Measure&TopN Selection. If you take a look at it and see any part that you'd be interested in learning more about, I'd love to hear it so I can write a how-to post.  

Also you can go to  bipatterns.com for more techniques and user guides.

[avinash726]

Hi,

Nice Solution.

Can we have only one selection(like TOP 10) in the chiclet and on de-selection we can get all the records?

thanks,

Avinash

[dkay84_PowerBI]

Why not just use the built in TopN filter?

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-feature-summary/#topN

[Anonymous]

Thank you!!!!!!! It worked fo me

[Anonymous]

@dkay84_PowerBI

Because the user can't easily adjust that.  You may want to see which stores or items are actually driving the KPI. In addition, you can use it to create the bottom half of the visual I have there which is to see the TopN stores total contributition to the KPI, as well as Percent of total.  

The TopN function in Power BI is great for a set-it-and-forget-it report, but not if you want to let the user choose the the 'N' value themselves.

With this report, I also included a slicer that allows them to choose the KPI they want to see (Profit, Returns, Sales).  In order to make the TopN part respond in respect to the selected KPI, I went with DAX.  I guess what I'm trying to show is how to extend Power BI reports through some clever, yet simple DAX.

[dkay84_PowerBI]

Got it. Thanks for sharing I will definitely use this

[Phil_Seamark]

Sometimes it's just nice to have a button to press on the screen for the user rather than make them use the dropdowns on the side.

Nice article

[dkay84_PowerBI]

Plus if this is a published report where the page is pinned to a dashboard then there would be not dropdown filter on the side.

[Anonymous]

@dkay84_PowerBI

Excellent point.  I'm working on some blogs that outline some techniques for the Retail Analysis -Dynamic Measure&TopN Selection. If you take a look at it and see any part that you'd be interested in learning more about, I'd love to hear it so I can write a how-to post.  

Also you can go to  bipatterns.com for more techniques and user guides.