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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Issues with rankx

Hello. I have 5 product categories and i want to rank them from 1 to 5 according to their sales but the data integrity is not accurate 100% and I would like to ask for some help.

This is my dax function for the Sum:

SumOrders = CALCULATE(IF(ISBLANK(SUM(FactOrders[Total])), 0, SUM(FactOrders[Total])))
Rankx dax:
Sales Amount Product Category Rank =
RANKX ( ALL ( dimProducts[Category_Name]), [SumOrders],, DESC )
 
Store Rank Text = [Sales Amount Product Category Rank] & " of "   & 5
 this is the full display of the ranks
IdanTrial12_0-1705391050977.png

 

when I will pick components and clothing both of them will have rank 3

 IdanTrial12_1-1705391089830.pngIdanTrial12_2-1705391111523.png

how can I solve this issue?? Thanks in advance

 

6 REPLIES 6
Anonymous
Not applicable

Hi @Anonymous ,
Based on the information you provided, you can follow this step below:
1.Add new measure

 

Sales Amount Product Category Rank = 
RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[orders] ),
    CALCULATE ( MAX ( 'Table'[orders] ) ),
    ,
    DESC
)
Store Rank Text = 
[Sales Amount Product Category Rank] & " of " & 5

 

 

Final output:

 

vyifanwmsft_0-1705470057947.png

vyifanwmsft_1-1705470076286.png

 

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

 

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

it helps but for some reason i dont know how to adjust my table for your solution
I dont know how to upload the file ... I am new to this platform

https://app.powerbi.com/groups/b19193eb-e902-4d4f-8151-d6dba74e6c6d/reports/017d5641-90c5-47bb-a0ff-... 

 

i hope this help somehow.


 

 

Anonymous
Not applicable

Hi @Anonymous ,

 

You can put your files in a dropbox, onedrive or googledrive folder and share the link with the community. Please provide more details with your desired out put and pbix file without privacy information.

 

Please refer to the related links for details:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

How to Get Your Question Answered Quickly - Microsoft Fabric Community

 

Best Regards,

Ada Wang

Anonymous
Not applicable

its 119000 records so I cant do the sample data option

this is the file via my google drive:

https://drive.google.com/file/d/1BnhIMW-kr5ZTIXw49ga2gLCakG1MWB5_/view?usp=drive_link

some_bih
Super User
Super User

Hi @Anonymous try to replace part ALL in

ALL ( dimProducts[Category_Name]) with ALLSELECTED ( dimProducts[Category_Name])





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Anonymous
Not applicable

unfortunately didnt help, it ranks every category as 1

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors