This report is meant to highlight design techniques such as Dynamic titles, using custom tooltips, and key card layout. In addition, it allows the user to choose the KPI that matters the most to them (Sales, Returns, or Profits). The report can be used to meet the needs of several end users. Each end user has their own KPI’s that are most important to their role, and this method eliminates the need to create multiple reports for each department.
Go To http://www.pandatagroup.com/blog or bipatterns.com for more techniques and user guides.
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. To start, I'll outline how to create the dynamic TopN part of this report.
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?
View the report here: https://community.powerbi.com/t5/Data-Stories-Gall
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 contributition to the KPI, as well as Percent of total. 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.
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(ALLSELEC
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 value.
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.
