Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi All,
I am trying to do ranking for the inner row for the matrix table and need help where I am stuck for several days on this.
Sample Data:
Country | Area | Sales |
US | A | $ |
US | B | $ |
Australia | G | $ |
US | C | $ |
US | D | $ |
China | H | $ |
US | E | $ |
US | F | $ |
US | G | $ |
China | H | $ |
China | I | $ |
China | J | $ |
US | A | $ |
China | K | $ |
China | L | $ |
China | M | $ |
Australia | N | $ |
Australia | O | $ |
US | A | $ |
Australia | P | $ |
Australia | Q | $ |
Australia | R | $ |
Australia | S | $ |
I used this DAX function:
==============================================
Result Received:
Country/Area | Sales | Rank (Result Received) |
US | 490000 | 1 |
A | 100000 | 1 |
G | 90000 | 1 |
C | 80000 | 1 |
D | 70000 | 3 |
E | 60000 | 3 |
F | 50000 | 4 |
B | 40000 | 1 |
China | 330000 | 2 |
H | 80000 | 1 |
M | 70000 | 1 |
J | 60000 | 1 |
K | 50000 | 1 |
L | 40000 | 1 |
I | 30000 | 1 |
Australia | 210000 | 3 |
N | 60000 | 1 |
S | 50000 | 2 |
P | 40000 | 1 |
G | 30000 | 1 |
R | 20000 | 1 |
O | 10000 | 4 |
Intended Result:
Country/Area | Sales | Intended result |
US | 490000 | 1 |
A | 100000 | 1 |
G | 90000 | 2 |
C | 80000 | 3 |
China | 330000 | 2 |
H | 80000 | 1 |
M | 70000 | 2 |
J | 60000 | 3 |
Australia | 210000 | 3 |
N | 60000 | 1 |
S | 50000 | 2 |
P | 40000 | 3 |
Appreciate all the help on this 🙂
Hi @FreemanZ ,
I tried your suggestion, the result is kinda the same but filter out more data.
I still have multiple "area" (rows) ranking 1,2 and 3.
I also forgot the mention, the data set provided here is the final version after being transformed in the query.
User | Count |
---|---|
21 | |
19 | |
12 | |
10 | |
9 |
User | Count |
---|---|
30 | |
25 | |
15 | |
13 | |
10 |