How to find Top Values in Power BI using DAX
In the world of speed, the normal paradigm is to present information to your target audience in a creative way to maximize data consumption for enabling faster decision making. Data Visualization tools, by incorporating data, establish credibility and by adding interactive, eye catching elements increase engagement.
Imagine you are a Retail Sales Manager who is interested in quickly identifying:
• Top 5 Salespersons per their contribution to Sales
• Top 10 Products per Region
• Top 3 High Value Customers for a specific Financial Period.
• Top Channels per Revenue in the year 2017 and slice it Quarter wise.
Power BI helps you immediately identify your top values and dynamically generate reports to power you with actionable insights and help you plan your strategy ahead.
QUICK STEPS
Assumption – Let us assume we have 2 tables – Manager and Sales
Step 1 – Create a table named “Ranking Selection” for specifying your Top Ranks
For example, you could create a Ranking Selection Table with values – 3,5 and 10. These values can then be used as a slicer for users to select whether they want to see the Top 3, Top 5 or Top 10 Managers or categories in their reports. Make sure that this table does not have a relationship with any other table.
Step 2 – Create a new Measure for capturing the selections of the above Ranks [based on the Slicer selection]
In this case a measure named Rank Select has been defined.
Rank Select = IF (HASONEVALUE( ‘Ranking Selection'[Rank] ), VALUES(‘Ranking Selection'[Rank]),15 )
Step 3 – Create another set of Measures
Manager Ranking = RANKX (ALL ( Managers[Manager] ), Sales[Total Sales],, DESC )
This Measure is required for returning a table with the list of managers with an assigned Rank based on the Descending order of the Total Sales.
Sales by Manager = IF ([Manager Ranking]<=[Rank Select],[Total Sales],BLANK())
This Measure will help display Dynamic reports/visuals based on the Rank selection made. For instance – by selecting Rank 5 from the list, this measure will generate the Total Sales for the Top 5 Managers per Total Sales.
Step 4 – Create a Slicer based on Rank Column from Rank Selection Table
You can decide whether you would like to view the Ranks as a List or a Drop down. Once you have this slicer on the Report Canvas you can view the Top values based on this selection.
Step 5 – Go ahead and start creating your visuals.
You could start with a simple Bar chart and jazz it up with infographics custom visuals. You could build this visual on Manager Name and Sales by Manager Measure.
Similarly, you could identify the Top Categories of products by sales and link it with the Ranking Measure. If you want to view the Top 5 Managers per sales as well as the Top 5 Categories per sales, you can make a Ranking selection and get equipped with solid intelligence on your best performers.
Conclusion:
Identifying Top values per any data category becomes easy in Power BI by leveraging DAX. By leveraging DAX, you can add Dynamic visuals, Dynamic headings supporting those visuals, Labels and capture relevant data that you want to see to gain quick insights from your Data. There are many other DAX functions like TOPN that can also be leveraged for producing similar reports. It is not a surprise anymore that DAX can help you solve all complex business scenario problems with just a few clicks of a button in Power BI.
Stay tuned for more learnings.
Blog disclaimer: This is a professional weblog, and we have invited experts to share their thoughts, expertise , perspectives and knowledge. The opinions expressed here are purely representing their personal views and not those of any institution, employer or company.
“Extend your learning with our specialized courses"
info@voksedigital.comSource: Microsoft Power BI
