Whether its an online Power BI training or onsite Power BI training, DAX studio is a part of every Power BI DAX training. DAX studio has earned its way through the user-friendly interface that makes query building easier then every before. Here’s a blog series dedicated to this wonderful tool: DAX Studio Archives | Power BI Training Australia

In this exercise, you will learn how to categorize monthly sales for a product as low, medium, high and very high using SUMMARIZECOLUMNS() function.

This function provides more capabilities and was introduced by Microsoft as a replacement to SUMMARIZE().  

To categorize the products:

1. Open your Power BI desktop model.
2. Right click on the Sales table in the Fields Pan.
3. Click on New Column.

Column tools open.

4. Type the following formula in the formula bar and press Enter
5. Click on External Tools in the Ribbon.
6. Click on DAX Studio.
7. Erase the existing code in the Query Pan.
8. In the Query Pan, type the following code.
9. Click on Run in the File Tab.

The Results are generated in the Results Pan.

10. Copy the Code from line 3-12 in DAX Studio.
11. Open Power B Desktop.
12. Click on the Modeling tab in the Ribbon.
13. Click on New Table.
14. Paste the code in the formula bar and press Enter

Notice that we are filtering the product table and not including Accessories category.

Now let’s create a calculated column in “High value Sales (CT)” to classify the sales.

14. Right click on the High value Sales (CT) table in the Fields Pan.
15. Click on New Column.

Column tools open. 

15. Type the following formula in the formula bar and press Enter.
16. Click on Total Sales in High value Sales (CT) table in the Fields Pan.

Column Tools Open.

17. Click on “$” and type “2” in the text box in formatting section.
18. Click on the Model tab in the left ribbon.
19. Drag the End of Month (CC) column from High Value Sales (CT) and drop to the Date in the Date table.

Please note that a many to one relationship between High Value Sales (CT) and Date table is created.

20. Drag the ProductKey column from High Value Sales (CT) and drop to the ProductKey in the Product table.

Please note that a many to one relationship between High Value Sales (CT) and Date table is created.

21. Click on Report View on the left Ribbon.
22. Click on the matrix visual in the visualization pane.
23. Drag and drop Category and Subcategory from the Product table to the rows.
24. Drag and drop Total Sales from High Value Sales (CT) in Values
25. Click on Slicer visual in the Visualization Pane.
26. Drag and drop Sales Value Classification (CC) from High Value Sales (CT) to the Fields.
27. Click on Slicer visual in the Visualization Pane.
28. Drag and drop Year and Month Name from Date to the Fields.
29. Click on the Arrow at the top and select List.
30. In the Year, Month Name Slicer, Select 2017>Oct,Nov,Dec.
31. In the Sales Value Classification (CC) slicer, Select 2) Medium.

Conclusion:

Categorizing low, medium and high value products is important for every business. It helps the company to analyze the trends, forecast sales and thereby, make smarter decision based on facts and figures. This blog post is dedicated to categorizing low, medium and high sales using the DAX studio with Power BI desktop.

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