COUNTROWS in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to know how many sales transactions happened each month to understand sales activity volume.

📊 Data: You have a sales data table with columns: TransactionID, Date, Product, Quantity, and SalesAmount.

🎯 Deliverable: Create a report showing the count of sales transactions per month.

Sample Data

TransactionID	Date	Product	Quantity	SalesAmount
1001	2024-01-05	Shirt	2	40
1002	2024-01-15	Pants	1	50
1003	2024-02-03	Hat	3	30
1004	2024-02-20	Shirt	1	20
1005	2024-03-10	Shoes	1	80
1006	2024-03-15	Shirt	2	40
1007	2024-03-25	Pants	1	50
1008	2024-04-05	Hat	2	20
1009	2024-04-18	Shoes	1	80
1010	2024-04-22	Shirt	1	20

1

Step 1: Create a new calculated column to extract the month and year from the Date column for grouping.

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

Expected Result

A new column 'MonthYear' with values like '2024-01', '2024-02', etc.

2

Step 2: Create a measure to count the number of sales transactions for each month.

MonthlyTransactionCount = COUNTROWS('Sales')

Expected Result

A measure that counts all rows in the Sales table filtered by the current month context.

3

Step 3: Build a table visual with 'MonthYear' on rows and the 'MonthlyTransactionCount' measure as values.

Configure table visual: Rows = 'Sales'[MonthYear], Values = MonthlyTransactionCount

Expected Result

Table showing each month and the count of transactions: 2024-01 = 2, 2024-02 = 2, 2024-03 = 3, 2024-04 = 3

Final Result

MonthYear | Transactions
------------------------
2024-01   | 2
2024-02   | 2
2024-03   | 3
2024-04   | 3

✓January and February each had 2 sales transactions.

✓March and April had higher sales activity with 3 transactions each.

✓Sales volume increased in March and April compared to the first two months.

Bonus Challenge

Create a measure to count only transactions where Quantity is greater than 1 for each month.

Show Hint

Use COUNTROWS with FILTER to count rows where 'Sales'[Quantity] > 1.