- Analyzing top ‘N’ products and customers by sales revenue
- To get the top three products by each subcategory by sales revenue
- To find high value sales by product or customers by month
- TOPN
- SUMMARIZE
- SUMMARIZECOLUMNS
- GENERATE
- CALCULATETABLE
TOPN()
We will use TOPN() to find the top 10 products by sales revenue. Here is its syntax.
TOPN() needs a table (it can be a table expression) to iterate it row by row to find the desired top rows. We will use SUMMARIZE() to create a table expression for product wise sales amount.
SUMMARIZE()
The DAX function SUMMARIZE() aggregates and groups data by leveraging existing relationships in your data model. Its general pattern is as follows:
Let’s open our sample Power BI desktop (PBI) file and go to External Tools ribbon to launch DAX Studio. We will generate and test our code there and then will move it to PBI.
- In the Query Pan, type the following code and click Run.
2. You can see the result of the query in Result pan.
The Query generated in DAX Studio is always a table. If you want to create a calculated table in PBI you can copy the code highlighted in number 3, go to PBI, in Modeling Tab, click on new table and past the code there. In our scenario, we are going to create a calculated measure so that it can be further sliced and diced. For doing so, we will have to use an iterator such as SUMX().
- Go to All Measures table (the table we are using to save all our calculated measures)
- Right click and select New Measure
- Paste the code copied from DAX Studio in SUMX() as table and use ‘Total Sales Amount (CM)’ measure for the expression.
4. Go to Measure Tools and apply currency format with two decimals.
5. Click on Matrix visual.
6. Add Category and Subcategory from Product table to Rows
7. Add ‘Total Sales Amount (CM)’ and ‘Top 10 Products by Total Sales Amount (CM)’ from All Measures to Values, sort by ‘Total Sales Amount (CM)’ and expand Bikes category.
Notice that contribution of the top 10 products in each category and subcategory is being shown.
8. You can also calculate the percent contribution of top 10 products to the total sales revenue. To do that, follow the step 1 & 2 mentioned above, use the following code to create another calculated measure and format it as percentage.
9. Add the ‘% of Total Sales (CM)’ to the values
Notice that the percent contribution of to 10 products sales is being shown in the visual.
Categorizing Low, Medium and High-value Sales
We want to categorize product wise sales by month as low, medium, high and very high. We will create a new data table with the help of SUMMARIZECOLUMNS() function.
SUMMARIZECOLUMNS()
This function provides more capabilities and was introduced by Microsoft as a replacement to SUMMARIZE(). Following table specifies some of the differences of the two functions.
Let’s do this with the help of DAX Studio. Before generating the code, we will create a calculated column (CC) in Sales Data table to get the end of month date. Here is the expression for the CC.
- Launch the DAX Studio as mentioned in the last example, and write the following query. You will see the result on ‘Result’ pan.
Copy the code highlighted in number 1, go to Power BI Desktop, click on Modeling tab, create a new table and paste the code as given below.
Notice that we are filtering the product table and not including Accessories category.
You will then create a calculated column in “High value Sales (CT) ” to classify the sales. Write the following code in the column.
Our new summary data table is ready now and we will create relationships with Date and Product dimension tables. The data model will look like this.
Now go to report view, and on a new page, add a matrix visual. Add Category, subcategory and Product from Product table in Rows and Total Sales from High Value Sales (CT) in Values. Then add year and month slicers from Date table and ‘Sales Value Classification (CC)’ slicer from High Value Sales (CT).
Top three products in each product subcategory
In our next scenario, we are going to extract top three products in each subcategory. We will mainly apply GENERATE() and TOPN() functions to find the solution.
We will generate the following code in DAX Studio in the same way we did before.
Copy the code after EVALUATE, go to PBI and create a new calculated table by entering the code copied from DAX Studio. Your code should look like this.
By placing all the columns of this calculated table, in a table visual, the output is as follows.
Notice that for each subcategory, the top three products with sales amounts are showing.
CALCULATETABLE()
CALCULATETABLE() works same as CALCULATE() except it’s applied on table expression unlike CALCULATE() which is used to modify filter context applied to an expression that returns a scalar value.
Let’s find the sales of all categories where color of the product is Black. Here is the desired code generated in DAX Studio.
If we use this code, create a measure in Power BI Desktop as mentioned in first scenario and use SUMX(), our code will look like this.
By showing the color from Product table and the above measure along with Total Sales Amount (CM), in a table visual, we get the following result.
Notice that the total sales of Black color is being repeated in each row.
Conclusion:
DAX Studio is an excellent tool that help us in generating and testing our DAX queries. If we want to move the code to Power BI Desktop, we need to copy the code (after EVALUATE expression) and paste it in a new calculated table. If we want it to be a calculated measure, we need to wrap it inside any function which requires a table expression.
Learning Power BI from Scratch? Get a complete, in-depth training on Microsoft Power BI by attending our training courses.
More Checkouts for Power BI Training in Australia.
- Power BI Training in Sydney
- Power BI Training in Melbourne
- Power BI Training in Brisbane
