VALUES and DISTINCT in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to see a list of unique product categories sold last month and the total sales for each category.

📊 Data: You have a sales table with columns: Date, ProductCategory, SalesAmount.

🎯 Deliverable: Create a report showing unique product categories sold last month and their total sales.

Sample Data

Date	ProductCategory	SalesAmount
2024-05-01	Electronics	200
2024-05-03	Clothing	150
2024-05-05	Electronics	300
2024-05-10	Home	400
2024-05-15	Clothing	100
2024-05-20	Sports	250
2024-05-25	Home	350
2024-06-01	Electronics	500
2024-06-03	Sports	300

1

Step 1: Filter the sales data to include only rows where the Date is in May 2024.

FILTERED_SALES = FILTER(Sales, Sales[Date] >= DATE(2024,5,1) && Sales[Date] <= DATE(2024,5,31))

Expected Result

Filtered data contains only sales from May 2024.

2

Step 2: Create a new table of unique product categories sold in May 2024 using VALUES.

UniqueCategories = VALUES(FILTERED_SALES[ProductCategory])

Expected Result

UniqueCategories table contains: Electronics, Clothing, Home, Sports.

3

Step 3: Create a measure to calculate total sales for each product category in May 2024.

TotalSales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Date] >= DATE(2024,5,1) && Sales[Date] <= DATE(2024,5,31) && Sales[ProductCategory] = SELECTEDVALUE(UniqueCategories[ProductCategory])))

Expected Result

TotalSales measure sums sales amounts for May 2024 by product category.

4

Step 4: Build a table visual in Power BI with ProductCategory from UniqueCategories and TotalSales measure.

Create a one-to-many relationship from UniqueCategories[ProductCategory] to Sales[ProductCategory]. Add UniqueCategories[ProductCategory] to rows and TotalSales measure to values in the table visual.

Expected Result

Table shows each unique product category sold in May 2024 with its total sales.

Final Result

ProductCategory | TotalSales
----------------|----------
Electronics     | 500
Clothing       | 250
Home           | 750
Sports         | 250

✓Four unique product categories were sold in May 2024.

✓Home category had the highest total sales of 750.

✓Electronics and Sports had moderate sales.

✓Clothing had the lowest total sales among these categories.

Bonus Challenge

Create a measure that uses DISTINCT to count how many unique product categories had sales above 200 in May 2024.

Show Hint

Use DISTINCTCOUNT with a filter on SalesAmount > 200 and Date in May 2024.