cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NA Regular Visitor
Regular Visitor

DAX Measures Top Percentiles and Rank

Yikes! Gosh I'm so new to this. 

 

I have a table with the following: 

 

Column A | Column B | Column C 

Column A containts unique ID numbers associated to a member 

Columb B contains the most recent year in which we appraised their performance [2018] 

Column C contains the performance score 

 

I'm trying to create two new measures: 

 

A precentile column measure and a rank column measure. Any help on how to write this dax? I've looked around the forum and read the microsoft doc, but gettting stuck with the DAX language. 

 

Here's what the table looks like atm. 

Table Sample.PNG

 

Thanks, 

6 REPLIES 6
tejaswidmello Established Member
Established Member

Re: DAX Measures Top Percentiles and Rank

Hi @NA ,

 

To get Rank Measure:

use this formula:

Rank = RANKX(ALLSELECTED('Table (3)'),CALCULATE(SUM('Table (3)'[COl C]),ALLEXCEPT('Table (3)','Table (3)'[COl A])),,DESC,Dense)

 

To get the percentile  use this measure

Percentile = ( COUNTAX ( ALLSELECTED ( 'Table (3)' ), 'Table (3)'[COL A]) - [Rank] )
    / ( COUNTAX ( ALLSELECTED( 'Table (3)'), 'Table (3)'[COL A] ) - 1 )

Please in the above formulas use your Columns names and Table Names.

Let me know if this works for you.

 

Thanks,

Tejaswi

 

NA Regular Visitor
Regular Visitor

Re: DAX Measures Top Percentiles and Rank

@tejaswidmello  - It appears that it returned for both the rank measure and precentile measure it returned 1. Another thing to note, when looking at the data in a table format, it's repeating for some reason . . .. 

 

 

Ew.PNG

tejaswidmello Established Member
Established Member

Re: DAX Measures Top Percentiles and Rank

Hi @NA ,

 

Would it be possible to provide your sample data in a excel spreadsheet?

Save your sample file in a dropbox and share a link of that file.

 

I will have to see whats wrong in the formula and why the data is repeating .

 

 

Thanks,

Tejaswi

Community Support Team
Community Support Team

Re: DAX Measures Top Percentiles and Rank

Hi @NA 

Create two measures

precentile = SUM('Table'[score])/CALCULATE(SUM('Table'[score]),ALL('Table'))

rank = RANKX(ALL('Table'),CALCULATE(SUM('Table'[score])),,DESC,Dense)

Capture27.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team
Community Support Team

Re: DAX Measures Top Percentiles and Rank

Hi @NA 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

 

Best Regards

Maggie

NA Regular Visitor
Regular Visitor

Re: DAX Measures Top Percentiles and Rank

Maggie, for the most part this works. But when I use slicers to filter by the product category [text] the calculation doesn't adjust within that category. 

 

Is it possible to modify the dax to calculate based on the product category? [for E.G. when i filter to apples, it carries over the calculation of the whole column of performance, I'd like it to adjust the calculation by category as well). 

 

Thank you btw. The overall percentile and rank dax code works, if we can only get it to adjust the calculations based on the filtered category, we'll be golden!! 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 4,104 guests
Please welcome our newest community members: