There’s no doubt that 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 (Data Analysis Expressions.) The trick I’ll outline below is how to create a Dynamic TopN report using DAX, which will take your Power BI reports to the next level. 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 has a built in TopN function, but it isn't easy/intuitive for the user to select. 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. 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 above I have there which is to see the TopN stores’ total contribution to the KPI, as well as Percent of Total. Sometimes it's just nice to have a button for the user to press on the screen rather than make them use the dropdowns on the side.
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 the one below:
Top
5
10
15
20
30
50
This table and field will be used as a Slicer in the report, and the user’s 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 whether the user has selected a value or not. If they have, then it will use the number they’ve selected, otherwise it will set the default value to 10.
Let’s say we’re looking to see the TopN contribution for Stores. 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 stores that the user has selected. For example, if the company has stores in all 50 states, but the user has selected Wisconsin in a slicer, we want the measure to rank each store only within 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 value.
Have any questions? I'm happy to help and you can reach me here at Pandata Group. Plus, visit my personal blog where I offer more tips and tricks at www.bipatterns.com.
About the Author:
Ryan Durkin is a BI Consultant with Pandata Group and works with clients who want greater insight into spreadsheets and information with data visualization. He is a leader within the Microsoft practice and well-versed in Power BI. If you have any questions for Ryan, please email him at rdurkin@pandatagroup.com.
