cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dynamic TopN reports in Power BI

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/121080https://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:

 TopN Table formatting.png

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.

8 REPLIES 8
avinash726
Frequent Visitor

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
Microsoft
Microsoft

Anonymous
Not applicable

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

Anonymous
Not applicable

@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.

Got it. Thanks for sharing I will definitely use this

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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
Not applicable

@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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors