Why DAX powers calculations in Power BI - Business Case Study

Scenario Mode

👤 Your Role: You are a business analyst working with sales data in Power BI.

📋 Request: Your manager wants you to create dynamic calculations to analyze sales performance by product and region.

📊 Data: You have a sales table with columns: Date, Region, Product, Sales Amount, and Quantity Sold.

🎯 Deliverable: Create calculated measures using DAX to show total sales, average sales per product, and sales growth compared to the previous period. Then build a report with visuals showing these insights.

Sample Data

Date	Region	Product	Sales Amount	Quantity Sold
2024-01-01	North	Widget A	1000	10
2024-01-01	South	Widget B	1500	15
2024-02-01	North	Widget A	1200	12
2024-02-01	South	Widget B	1600	16
2024-03-01	North	Widget A	1100	11
2024-03-01	South	Widget B	1700	17
2024-01-01	East	Widget C	900	9
2024-02-01	East	Widget C	950	10
2024-03-01	East	Widget C	1000	11

1

Step 1: Create a calculated measure for Total Sales using DAX.

Total Sales = SUM('Sales'[Sales Amount])

Expected Result

Total Sales sums all sales amounts dynamically based on filters.

2

Step 2: Create a calculated measure for Average Sales per Product.

Average Sales per Product = DIVIDE([Total Sales], DISTINCTCOUNT('Sales'[Product]), 0)

Expected Result

Calculates average sales amount per product considering current filters.

3

Step 3: Create a calculated measure for Sales Growth compared to previous month.

Sales Growth % = DIVIDE([Total Sales] - CALCULATE([Total Sales], PREVIOUSMONTH('Sales'[Date])), CALCULATE([Total Sales], PREVIOUSMONTH('Sales'[Date])), 0) * 100

Expected Result

Shows percentage growth or decline in sales compared to the previous month.

4

Step 4: Build a report with a line chart showing Total Sales over time by Region.

Configure line chart: Axis = Date, Legend = Region, Values = Total Sales measure

Expected Result

Line chart displays sales trends over months for each region.

5

Step 5: Add a table visual showing Product, Total Sales, Average Sales per Product, and Sales Growth %

Table columns: Product, Total Sales, Average Sales per Product, Sales Growth %

Expected Result

Table shows sales metrics per product with dynamic calculations.

Final Result

---------------------------------------------
| Date       | North | South | East  | Total |
|------------|-------|-------|-------|-------|
| 2024-01-01 | 1000  | 1500  | 900   | 3400  |
| 2024-02-01 | 1200  | 1600  | 950   | 3750  |
| 2024-03-01 | 1100  | 1700  | 1000  | 3800  |
---------------------------------------------

Product Summary Table:
-------------------------------------------------
| Product  | Total Sales | Avg Sales/Product | Growth % |
|----------|-------------|-------------------|----------|
| Widget A | 3300        | 1100              | 5.0%     |
| Widget B | 4800        | 1600              | 6.25%    |
| Widget C | 2850        | 950               | 5.26%    |
-------------------------------------------------

✓Sales increased steadily from January to March across all regions.

✓Widget B has the highest total sales and average sales per product.

✓Sales growth percentage shows positive trends month over month.

Bonus Challenge

Create a DAX measure to calculate the cumulative total sales year-to-date.

Show Hint

Use the TOTALYTD function with the Date column and Total Sales measure.