DAX Measures Top Percentiles and Rank

[Anonymous]

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. 

Thanks, 

[v-juanli-msft]

Hi @Anonymous 

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

[v-juanli-msft]

Hi @Anonymous 

Create two measures

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

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

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.

[Anonymous]

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!! 

[Anonymous]

Hi @Anonymous ,

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

[Anonymous]

@Anonymous  - 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 . . .. 

[Anonymous]

Hi @Anonymous ,

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