cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rdurkin Member
Member

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?

 Power BI Pandata Report - TopN.pnghttps://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
dkay84_PowerBI New Contributor
New Contributor

Re: Dynamic TopN reports in Power BI

rdurkin Member
Member

Re: Dynamic TopN reports in Power BI

@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 New Contributor
New Contributor

Re: Dynamic TopN reports in Power BI

Got it. Thanks for sharing I will definitely use this
Phil_Seamark Super Contributor
Super Contributor

Re: Dynamic TopN reports in Power BI

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!

dkay84_PowerBI New Contributor
New Contributor

Re: Dynamic TopN reports in Power BI

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.
rdurkin Member
Member

Re: Dynamic TopN reports in Power BI

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

odeyneko Frequent Visitor
Frequent Visitor

Re: Dynamic TopN reports in Power BI

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

avinash726 Frequent Visitor
Frequent Visitor

Re: Dynamic TopN reports in Power BI

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 355 members 3,714 guests
Please welcome our newest community members: