cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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:

 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:

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

and use filter to filter up top 3 sales.

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

 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 🙂

2 REPLIES 2
Super User

try like:

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

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors