RANKX for ranking 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 ranking of salespeople based on their total sales for the last quarter.

📊 Data: You have a sales data table with columns: Salesperson, Region, SalesAmount, and Date.

🎯 Deliverable: Create a report that shows each salesperson's total sales and their rank from highest to lowest sales.

Sample Data

Salesperson	Region	SalesAmount	Date
Alice	North	1200	2024-01-15
Bob	South	1500	2024-02-10
Charlie	East	1100	2024-03-05
Alice	North	1300	2024-03-20
Bob	South	1700	2024-01-25
Charlie	East	900	2024-02-15
Diana	West	1600	2024-01-30
Diana	West	1400	2024-03-10

1

Step 1: Create a new measure to calculate total sales per salesperson for the last quarter.

Total Sales = CALCULATE(SUM('Sales'[SalesAmount]), DATESBETWEEN('Sales'[Date], DATE(2024,1,1), DATE(2024,3,31)))

Expected Result

Total sales sums for each salesperson between January 1, 2024 and March 31, 2024.

2

Step 2: Create a ranking measure using RANKX to rank salespeople by their total sales in descending order.

Sales Rank = RANKX(ALL('Sales'[Salesperson]), [Total Sales], , DESC, DENSE)

Expected Result

Each salesperson gets a rank number where 1 is the highest total sales.

3

Step 3: Build a table visual in Power BI with columns: Salesperson, Total Sales, and Sales Rank.

Add fields: 'Salesperson', 'Total Sales' measure, and 'Sales Rank' measure to the table visual.

Expected Result

A table showing each salesperson's name, their total sales for the last quarter, and their rank.

Final Result

Salesperson | Total Sales | Sales Rank
------------|-------------|-----------
Bob         | 3200        | 1         
Diana       | 3000        | 2         
Alice       | 2500        | 3         
Charlie     | 2000        | 4

✓Bob has the highest total sales with 3200.

✓Diana is ranked second with 3000 in sales.

✓Alice and Charlie follow in third and fourth place respectively.

Bonus Challenge

Modify the ranking to reset by Region, so each region has its own sales ranking.

Show Hint

Use RANKX with FILTER to rank salespeople within each region separately.