Top 10 Products Contribution To Sales Revenue

Products contribution to sales revenue is a key metric in Power BI, and DAX introduces users to a whole new world. DAX Studio simplifies understanding this world. Therefore, we include DAX concepts in every Power BI training in Australia, depending on the training level. In this blog post, you will discover how to use TOPN() to identify the top 10 products by sales revenue in DAX Studio.

Before we start, lets quickly understand the syntax and functionality of TOPN():

 You will need to connect with Power BI desktop model with DAX studio first. If you’re new to this, here’s a link to help you through the process:

Getting Started with DAX Studio

Understanding TOPN() in DAX

TOPN() requires a table (or a table expression) to iterate row by row and determine the top rows.

Using SUMMARIZE() for Aggregation

To achieve this, we will first use SUMMARIZE() to create a table expression for product-wise sales amounts. Moreover, the SUMMARIZE() function aggregates and groups data by effectively leveraging existing relationships in your data model.

Top 10 Products by Sales

To find Top 10 Product’s contribution to Sales Revenue:

1. Open your DAX file.
2. Click on External Tools tab in the Ribbon.
3. Click on DAX Studio.

Please close the query builder if it is already open.

4. Erase the existing code in the Query Pan.
5. In the Query Pan, type the following code.
6. Click on Run in the File Tab.

The Results are generated in the Results Pan.

Creating a Calculated Table in Power BI

The Query generated in DAX Studio is always a table.

To create a calculated table in Power BI:

7. Copy the Code from line 2-10 in DAX Studio.
8. Open Power B Desktop.
9. Click on the Modeling tab in the Ribbon.
10. Click on New Table.
11. Paste the code in the formula bar and press Enter

Creating a Calculated Measure

Let’s create a calculated measure using an iterator so that data can be further sliced and diced.

12. Right click on Testing DAX Studio (CT) Table in the Fields.
13. Click on New measure.

Measure tools open.

14. Type the following formula in the formula bar and press Enter.
15. Click on the “$” and type 2 in the box.
16. Click on the matrix visual in the visualization pane.
17. Drag and drop Category and Subcategory from the Products table to the rows.
18. Drag and drop Total Sales Amount (CM) from All Measures to the Values
19. Drag and drop Top 10 Products by Sales Amount (CM) from Testing DAX Studio (CT) to the Values.
20. Expand Bikes Category.
    

     

Notice that contribution of the top 10 products in each category and subcategory is being shown.

Finding Percentage Contribution

To find the percentage contribution:

21. Right click on Testing DAX Studio (CT) Table in the Fields.
22. Click on New measure.

Measure tools open.

23. Type the following formula in the formula bar and press Enter.
24. Click on % in the formatting section.
25. Drag and drop Testing % of Total Sales (CM) from Testing DAX Studio (CT) to the Values.

Notice that the percent contribution of to 10 products sales is being shown in the visual.

For advanced scenarios like the one presented in this blog:  https://powerbitraining.com.au/advanced-scenarios-with-dax-studio-power-bi-advanced-dax-scenarios-with-dax-studio/

Conclusion:

DAX Studio helps users understand the query-building process in DAX and easily solve advanced business problems. For instance, one common challenge is determining the contribution of a company’s top products. In this blog, we present a step-by-step guide to solving this problem effectively.

Are you looking for an instructor-led Power BI or Power Apps training in Australia? We provide classroom as well as online live trainings for the following courses:

• Power BI Essentials
• Power BI Advanced
• DAX Essentials
• Power Apps Essentials