TOPN function 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 the top 3 products by total sales amount for the last quarter to focus marketing efforts.

📊 Data: You have sales data including Product Name, Sales Date, Quantity Sold, and Sales Amount.

🎯 Deliverable: Create a report showing the top 3 products by total sales amount for the last quarter with their sales figures.

Sample Data

Product Name	Sales Date	Quantity Sold	Sales Amount
Alpha	2024-01-15	10	500
Beta	2024-02-10	5	300
Gamma	2024-03-05	8	400
Alpha	2024-03-20	7	350
Beta	2024-01-25	6	360
Delta	2024-02-15	4	200
Gamma	2024-01-30	3	150
Alpha	2024-02-05	9	450
Delta	2024-03-10	5	250
Beta	2024-03-25	7	420

1

Step 1: Filter the sales data to include only sales from the last quarter (January to March 2024).

Use a date filter on Sales Date column for dates between 2024-01-01 and 2024-03-31.

Expected Result

Filtered data contains only sales records from Q1 2024.

2

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

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

Expected Result

Measure sums sales amounts for each product.

3

Step 3: Use the TOPN function to find the top 3 products by total sales amount.

Top 3 Products = TOPN(3, SUMMARIZE('Sales', 'Sales'[Product Name], "TotalSales", [Total Sales]), [TotalSales], DESC)

Expected Result

A table with the top 3 products and their total sales amounts.

4

Step 4: Create a table visual in Power BI with Product Name and Total Sales from the Top 3 Products measure.

Add a table visual with columns: Product Name, Total Sales using the Top 3 Products table.

Expected Result

Table shows top 3 products with their total sales amounts.

Final Result

Top 3 Products by Sales (Q1 2024)
+-------------+-------------+
| Product Name| Total Sales |
+-------------+-------------+
| Alpha       | 1300        |
| Beta        | 1080        |
| Gamma       | 550         |
+-------------+-------------+

✓Alpha is the top-selling product with total sales of 1300.

✓Beta is the second top product with total sales of 1080.

✓Gamma ranks third with total sales of 550.

✓Delta is not in the top 3 for this quarter.

Bonus Challenge

Create a dynamic slicer to select any quarter and show the top 3 products for the selected quarter.

Show Hint

Use a Date table with quarters and modify the TOPN measure to filter based on the selected quarter from the slicer.