- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ranking for Main Category then furthermore 3 sub-categories based on %
Dear Sirs & Experts,
I want a DAX measure that can calculate Ranking for Main Category then furthermore 3 sub-categories based on %.
Necessary Reference Files and expected results is attached below link (Excel and pbix):
https://drive.google.com/file/d/1XOUWo-EyXJVcT1_6LPCWgc38pn0dkUnP/view?usp=sharing
OLD ISSUE:
Jihwan_Kim (The Super User of community) have solve the issue before and give solution but now a little bit change in design of dashboared therefore the below DAX measure is not work properly (when i used old below give DAX measure with New parameter command in Modeling Tab by filtered Region, Zone, Branhes and Area Sales Offier seperattarly (Pic attached) then Dax measure missed some rankings in Area sales officer and branches but region and zone is OK, i try to fix it but i no idea why this happen.) ... please guide me how to solve hte issue:
Previous issue link: Solved: RANKX for Main Category then for furthermore 4 sub... - Microsoft Fabric Community
Rank = SWITCH ( TRUE (), ISINSCOPE ( ASO[ASO Name] ), RANK ( SKIP, ALL ( ASO[ASO Name], ASO[Branch], ASO[Zone], ASO[Region] ), ORDERBY ( CALCULATE ( SUM ( ASO[Sales Ach] ) ), DESC ) ), ISINSCOPE ( ASO[Branch] ), RANK ( SKIP, ALL ( ASO[Branch], ASO[Zone], ASO[Region] ), ORDERBY ( CALCULATE ( SUM ( ASO[Sales Ach] ) ), DESC ) ), ISINSCOPE ( ASO[Zone] ), RANK ( SKIP, ALL ( ASO[Zone], ASO[Region] ), ORDERBY ( CALCULATE ( SUM ( ASO[Sales Ach] ) ), DESC ) ), ISINSCOPE ( ASO[Region] ), RANK ( SKIP, ALL ( ASO[Region] ), ORDERBY ( CALCULATE ( SUM ( ASO[Sales Ach] ) ), DESC ) ) )
Necessary Reference Files and expected results is attached below link (Excel and pbix):
https://drive.google.com/file/d/1XOUWo-EyXJVcT1_6LPCWgc38pn0dkUnP/view?usp=sharing
Dashboard Main Working
Expected Results
@ Jihwan_Kim @Ashish_Mathur @amitchandak
@ FreemanZ
Solved! Go to Solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @123abc ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a measure.
Rank =
SWITCH (
TRUE (),
ISINSCOPE ( 'unique keys'[ASO Name] ),
RANK (
SKIP,
ALL ( 'unique keys'[ASO Name], 'unique keys'[Branch], 'unique keys'[Zone], 'unique keys'[Region] ),
ORDERBY ( CALCULATE ( SUM ('Sales data'[Sales Ach] ) ), DESC )
),
ISINSCOPE ( 'unique keys'[Branch] ),
RANK (
SKIP,
ALL ( 'unique keys'[Branch], 'unique keys'[Zone], 'unique keys'[Region] ),
ORDERBY ( CALCULATE ( SUM ('Sales data'[Sales Ach] ) ), DESC )
),
ISINSCOPE ( 'unique keys'[Zone] ),
RANK (
SKIP,
ALL ( 'unique keys'[Zone], 'unique keys'[Region] ),
ORDERBY ( CALCULATE ( SUM ( 'Sales data'[Sales Ach] ) ), DESC )
),
ISINSCOPE ( 'unique keys'[Region] ),
RANK (
SKIP,
ALL ( 'unique keys'[Region] ),
ORDERBY ( CALCULATE ( SUM ( 'Sales data'[Sales Ach] ) ), DESC )
)
)
(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank YOu Very Mcuh, Its work in great manner. May God Bless You Amin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @123abc ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a measure.
Rank =
SWITCH (
TRUE (),
ISINSCOPE ( 'unique keys'[ASO Name] ),
RANK (
SKIP,
ALL ( 'unique keys'[ASO Name], 'unique keys'[Branch], 'unique keys'[Zone], 'unique keys'[Region] ),
ORDERBY ( CALCULATE ( SUM ('Sales data'[Sales Ach] ) ), DESC )
),
ISINSCOPE ( 'unique keys'[Branch] ),
RANK (
SKIP,
ALL ( 'unique keys'[Branch], 'unique keys'[Zone], 'unique keys'[Region] ),
ORDERBY ( CALCULATE ( SUM ('Sales data'[Sales Ach] ) ), DESC )
),
ISINSCOPE ( 'unique keys'[Zone] ),
RANK (
SKIP,
ALL ( 'unique keys'[Zone], 'unique keys'[Region] ),
ORDERBY ( CALCULATE ( SUM ( 'Sales data'[Sales Ach] ) ), DESC )
),
ISINSCOPE ( 'unique keys'[Region] ),
RANK (
SKIP,
ALL ( 'unique keys'[Region] ),
ORDERBY ( CALCULATE ( SUM ( 'Sales data'[Sales Ach] ) ), DESC )
)
)
(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank YOu Very Mcuh, Its work in great manner. May God Bless You Amin.

Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.

User | Count |
---|---|
12 | |
9 | |
9 | |
6 | |
5 |
User | Count |
---|---|
18 | |
15 | |
10 | |
9 | |
8 |