cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

DAX Ranking data based on multiple categories

Hi,

 

I have a table of data that has macroeconomic data on countries over a period of years. I am trying to assign a rank to each country based on their value, while still taking into account the Year, Indicator, and Grouping columns AND skipping over any blank Values. The end result would read like the "Rank" column in the below sample picture. I've tried adapting lots of different forum posts to my problem but nothing has been successful and I just cannot seem to figure it out..I know it should be possible with some combination of the Rankx(Filter(All( functions.

Capture.PNG

 

To me the logic would be something like: If the Value is not blank then Rank the country's value against all other nonblank country values within the same Indicator, Grouping, and Year. 

 

I would also probably need to use RANKX function as I need a way to switch from ASC to DESC ranking depending on the Indicator, as for some indicators the lower value the better while others a higher value the better. 

 

Here is a link to the file: http://www.filedropper.com/samplefile_6

 

I really appreciate any help, thanks in advance.

 

-Raj

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

@rajivraina ,

 

You may refer to the measure below.

Rank Measure =
RANKX (
    CALCULATETABLE (
        VALUES ( 'Country Mapping File'[ALL Country] ),
        ALLSELECTED ( 'Country Mapping File'[ALL Country] )
    ),
    CALCULATE ( SUM ( 'TE Data'[Value 4 Column] ) ),
    ,
    DESC,
    DENSE
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Highlighted
Super User IV
Super User IV

@rajivraina I have similar solution at this post, you can change it as per your need.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted


@parry2k wrote:

@rajivrainaI have similar solution at this post, you can change it as per your need.


Hi @parry2k,

Your solution does seem similar however it seems to be ranking based on a measure instead of a calculated column. I tried adapting your formula to my case but it does not achieve the ranking result I want, do you mind seeing where I messed up?

Rank Measure = RANKX( 
    FILTER( 
        ALL( 'TE Data'[Year1], 'TE Data'[Category], 'TE Data'[Country] , 'TE Data'[Grouping Column]),
        'TE Data'[Year1] = MAX('TE Data'[Year1]) && 
        'TE Data'[Category] = MAX( 'TE Data'[Category]) &&  
        'TE Data'[Grouping Column] = MAX('TE Data'[Grouping Column])
    ), 
    CALCULATE(SUM('TE Data'[Value 4 Column])), , DESC, Dense
)

 

 

 

Thanks,

Raj

Highlighted

@rajivraina quick question, why you want to add this as column instead of measure? 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted


@parry2k wrote:

@rajivrainaquick question, why you want to add this as column instead of measure? 


@parry2k I think you misunderstood, I want to add this as a measure, but my values that I am ranking by are in a column-- all my data is in columns. In your previous post the person had values coming from a measure. so I had to adapt the expression part of the Rankx formula you had to include this and idk if that's why it is not working:

CALCULATE(SUM('TE Data'[Value 4 Column]))
Highlighted

@rajivraina what result you are getting? Are you adding rank calculation as measure or column?






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted


@parry2k wrote:

@rajivrainawhat result you are getting? Are you adding rank calculation as measure or column?


@parry2k I am getting all 1's. I am adding this as a measure. 

 

Here is a link to my file: http://www.filedropper.com/samplefile2

 

Highlighted

@rajivraina thanks for sharing the file but I'm not going to download from tht site with tons of adv. etc etc. please share thru onedrive/google drive.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted


@parry2k wrote:

@rajivrainathanks for sharing the file but I'm not going to download from tht site with tons of adv. etc etc. please share thru onedrive/google drive.


Fair enough, do you have an email I can share through onedrive?

 

Highlighted

My company doesn't allow that option due to security concerns 😞 has to be either within the organization or to specific email address.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors