cancel
Showing results for
Did you mean:
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.

Thanks,

6 REPLIES 6
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

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

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

## 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)```

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

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

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

Announcements

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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 4,104 guests
Recent signins: