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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.