Top 10 Product’s contribution to Sales Revenue

You are currently viewing Top 10 Product’s contribution to Sales Revenue

DAX opens a whole new world for Power BI users and DAX studio makes the understanding of this new world easier. For this reason, our every Power BI training in Australia covers the DAX concepts based on the level of the Power BI training. In this blog post, you will learn how to use TOPN() to find 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:

https://powerbitraining.com.au/getting-started-with-dax-studio/

TOPN() needs a table (or a table expression) to iterate row by row and calculate the top rows. We will use SUMMARIZE() to create a table expression for product wise sales amount. The DAX function SUMMARIZE() aggregates and groups data by leveraging existing relationships in your data model.  

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.

  1. Erase the existing code in the Query Pan.
  2. In the Query Pan, type the following code.
  3. Click on Run in the File Tab.

The Results are generated in the Results Pan.

The Query generated in DAX Studio is always a table.

To create a calculated table in Power BI:

  1. Copy the Code from line 2-10 in DAX Studio.
  2. Open Power B Desktop.
  3. Click on the Modeling tab in the Ribbon.
  4. Click on New Table.
  5. Paste the code in the formula bar and press Enter

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

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

Measure tools open.

  1. Type the following formula in the formula bar and press Enter.
  2. Click on the “$” and type 2 in the box.
  3. Click on the matrix visual in the visualization pane.
  4. Drag and drop Category and Subcategory from the Products table to the rows.
  5. Drag and drop Total Sales Amount (CM) from All Measures to the Values
  6. Drag and drop Top 10 Products by Sales Amount (CM) from Testing DAX Studio (CT) to the Values.
  7. Expand Bikes Category.

     

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

To find the percentage contribution:

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

Measure tools open.

  1. Type the following formula in the formula bar and press Enter.
  2. Click on % in the formatting section.
  3. 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 can be used to understand the query building process in DAX and solve advanced business problems easily. One such problem is finding the contribution of a company’s top products. In this blog, a step-by-step guide to solving this problem has been presented.

Learning Power BI from Scratch? Get a complete, in-depth training on Microsoft Power BI by attending our training courses.

Power BI Training in Sydney

Power BI Training in Melbourne

Power BI Training in Brisbane 

Leave a Reply