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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Chris2B
Helper I
Helper I

Ranking A Dax measure

Hi I need some help please.

 

I am trying to rank my Category in Power BI to display the ranking based on financial YTD.

 

I currently have a measure of Financial YTD as:

Financial YTD = CALCULATE([Revenue],DATESYTD('Calendar'[Date],"31/3"))
 
This is how I would like the informdation to display in Power BI, but I have taken this from Excel.

 

CategoryFinancial YTDRank
Bread£1,193,190.151
Fish£800,774.672
Rice£262,978.343
Chicken£222,767.084
Orange£148,020.175
Water£114,348.156
Spinach£97,020.197
Kettle£94,938.338
Milk£89,659.719
Cheese£71,610.0510

 

I have tried using the measure:

RankingTable =
RANKX (
    ALLSELECTED ( Category[Category] ),
    [Financial YTD]
)

 

It only provides me with the number 1 instead of desc columns.

 

Greatly appreciated.

 

Chris

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

4 REPLIES 4
v-rzhou-msft
Community Support
Community Support

Hi @Chris2B ,

 

I think Category should be a dim table which is related to your data table. I suggest you to use [Category] column from data table instead of from Category dimtable.

Measure:

RankingTable = 
RANKX (
    ALLSELECTED('Table'[Category]),
    [Financial YTD]
)

Data model:

vrzhoumsft_0-1681292846364.png

Result is as below.

vrzhoumsft_1-1681292861795.png

 

Best Regards,
Rico Zhou

 

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

 

Hi Rico, sorry when I tried this solution, it still gave me 1 all the way but I have now found the solution :).

amitchandak
Super User
Super User

@Chris2B , That measure you have any other not summarized column in visual other than Category[Category]

 

Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=25627s

Thanks for this, it really helped!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.