Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Zakk2000
Regular Visitor

Ranking and filetr for top N, for matrix table using DAX

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:

CountryAreaSales
US$
USB$
Australia G$
USC$
USD$
ChinaH$
USE$
USF$
USG$
ChinaH$
ChinaI$
ChinaJ$
US$
ChinaK$
ChinaL$
ChinaM$
AustraliaN$
AustraliaO$
US$
AustraliaP$
AustraliaQ$
AustraliaR$
AustraliaS$

 

I used this DAX function:

Rank = RANKX (ALL (Report[Area]), CALCULATE(SUM(Report[Sales])))
 
and use filter to filter up top 3 sales.
 Zakk2000_0-1674183223288.png

==============================================

Result Received:

Country/AreaSalesRank (Result Received)
US4900001
A1000001
900001
C800001
D700003
E600003
F500004
B400001
China3300002
H800001
M700001
J600001
K500001
L400001
I300001
Australia2100003
N600001
S500002
P400001
G300001
R200001
O100004

 

Intended Result:

Country/AreaSalesIntended result
US4900001
A1000001
900002
C800003
China3300002
H800001
M700002
J600003
Australia2100003
N600001
S500002
P400003

 

Appreciate all the help on this 🙂

2 REPLIES 2
FreemanZ
Super User
Super User

hi @Zakk2000 

try like:

RANKX(
    ALL(Report[Area]),
    CALCULATE(SUM(Report[Sales])),
    ,
    ,
    Dense
)

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.

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors