Why intermediate DAX solves business questions in Power BI - Business Case Study

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand monthly sales performance, compare it to last year, and identify top products driving growth.

📊 Data: You have sales data with columns: Date, Product, Region, Sales Amount, and Quantity Sold for the last two years.

🎯 Deliverable: Create a Power BI report with measures showing monthly sales, year-over-year growth, and top 3 products by sales.

Sample Data

Date	Product	Region	Sales Amount	Quantity Sold
2023-01-15	Widget A	North	1200	10
2023-01-20	Widget B	South	800	8
2023-02-10	Widget A	North	1500	12
2023-02-15	Widget C	East	700	7
2022-01-18	Widget A	North	1000	9
2022-01-25	Widget B	South	900	9
2022-02-12	Widget A	North	1300	11
2022-02-20	Widget C	East	600	6

1

Step 1: Create a Date table to enable time intelligence calculations.

Use Power BI's built-in Date table or create with DAX: Date = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date]))

Expected Result

A Date table covering all sales dates from 2022-01-18 to 2023-02-20.

2

Step 2: Create a measure to calculate total sales amount.

Total Sales = SUM(Sales[Sales Amount])

Expected Result

Total Sales measure sums all sales amounts correctly.

3

Step 3: Create a measure to calculate sales for the previous year for comparison.

Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Expected Result

Sales LY measure shows sales amount for the same period last year.

4

Step 4: Create a measure to calculate year-over-year sales growth percentage.

YoY Growth % = DIVIDE([Total Sales] - [Sales LY], [Sales LY], 0)

Expected Result

YoY Growth % measure shows the percentage change in sales compared to last year.

5

Step 5: Create a measure to rank products by total sales.

Product Rank = RANKX(ALL(Sales[Product]), [Total Sales], , DESC, DENSE)

Expected Result

Product Rank measure assigns ranks to products based on sales, with 1 being highest.

6

Step 6: Create a visual table showing Product, Total Sales, and Product Rank, filtered to top 3 products.

Use a table visual with filters: Product Rank <= 3

Expected Result

Table shows top 3 products by sales with their sales amounts and ranks.

7

Step 7: Create a line chart showing monthly Total Sales and Sales LY over time.

Axis: 'Date'[Month], Values: Total Sales and Sales LY measures

Expected Result

Line chart displays monthly sales trends and last year's sales for comparison.

Final Result

Monthly Sales Trend

Month | Total Sales | Sales LY | YoY Growth %
---------------------------------------------
Jan   | 2000        | 1900     | 5.26%
Feb   | 2200        | 1900     | 15.79%

Top 3 Products by Sales
-----------------------
Rank | Product  | Total Sales
-----------------------------
1    | Widget A | 5000
2    | Widget B | 1700
3    | Widget C | 1300

✓Sales increased by about 5% in January and nearly 16% in February compared to last year.

✓Widget A is the top-selling product driving most of the growth.

✓Widget B and Widget C follow as second and third top products.

Bonus Challenge

Create a measure to calculate the percentage contribution of each product to total sales dynamically.

Show Hint

Use DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales[Product]))) to get product sales divided by total sales.