COUNT and DISTINCTCOUNT in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to understand how many total sales transactions happened each month and how many unique customers made purchases each month.

📊 Data: You have a sales data table with columns: TransactionID, CustomerID, SaleDate, and Amount.

🎯 Deliverable: Create a report showing for each month the total number of sales transactions and the number of unique customers.

Sample Data

TransactionID	CustomerID	SaleDate	Amount
1001	C001	2024-01-05	150
1002	C002	2024-01-15	200
1003	C001	2024-01-20	100
1004	C003	2024-02-02	250
1005	C004	2024-02-10	300
1006	C002	2024-02-15	180
1007	C005	2024-03-01	220
1008	C001	2024-03-05	130
1009	C006	2024-03-10	170
1010	C005	2024-03-15	210

1

Step 1: Create a new column in Power BI to extract the month and year from the SaleDate column.

MonthYear = FORMAT(Sales[SaleDate], "YYYY-MM")

Expected Result

A new column 'MonthYear' with values like '2024-01', '2024-02', '2024-03' for each row.

2

Step 2: Create a measure to count total sales transactions per month.

Total Transactions = COUNT(Sales[TransactionID])

Expected Result

For January 2024: 3, February 2024: 3, March 2024: 4

3

Step 3: Create a measure to count distinct customers per month.

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Expected Result

For January 2024: 2, February 2024: 3, March 2024: 3

4

Step 4: Create a table visual in Power BI with MonthYear as rows, and add the two measures: Total Transactions and Unique Customers as values.

Table visual configuration: Rows = MonthYear, Values = Total Transactions, Unique Customers

Expected Result

A table showing each month with total transactions and unique customers counts.

Final Result

MonthYear | Total Transactions | Unique Customers
---------------------------------------------
2024-01   | 3                  | 2
2024-02   | 3                  | 3
2024-03   | 4                  | 3

✓January had 3 sales transactions from 2 unique customers.

✓February had 3 sales transactions from 3 unique customers.

✓March had the highest activity with 4 sales transactions from 3 unique customers.

Bonus Challenge

Create a line chart showing the trend of unique customers over the months.

Show Hint

Use MonthYear on the X-axis and the Unique Customers measure on the Y-axis.