cancel
Showing results for
Did you mean:
Frequent Visitor

## calculating percentiles from a measure// calculating nº of ranking positions

hello everybody,
I am developing a report in which I have to show a graph with the percentile of a dimension table to which a specific measure is applied. The purpose of the chart is to show the percentage of fields that have a lower value in the calculation of the measure.
As I have not found a specific DAX function that does this type of operation, I am developing a measure that performs this calculation. To do so, I have created a measure with which the records are ordered according to the value of the initial measure. Subsequently, I would have to divide this ranking position by the total number of positions that exist in the ranking (which may or may not coincide with the number of records in the dimension).
I don't quite know how to do the calculation to extract the total number of ranking positions.

The measures I currently have are as follows:
Ranking= CALCULATE(RANKX(ALLSELECTED(dim_field01),CALCULATE([NPS_NPS],ALLEXCEPT(dim_field01,dim_field01[FIELD])),,DESC,Skip))
Percentile= CALCULATE(1-(DIVIDE((([Ranking]-0.5),/*No. ranking positions*/))))

Could someone help me?

2 REPLIES 2
Community Support

Hi, @Ivan_Ramos

It's not very clear, can you explain it with specific examples? P

lease provide a sample data /relevant screenshots and share your expected results for further research.

Best Regards,
Community Support Team _ Eason

Frequent Visitor

I have 2 tables with the following structure:

the Dim_field01 table has stored the attributes of the NPS fact table.

the NPS fact table has the id of the attributes stored on the dim_field01 table and the grades of these attributes. More than one record may refer to the same attribute.

Also I have the measure [NPS_NPS], this measure calculate a kind of average of the grades for any attribute.

Now I need to get the percentile of the result of that measure, but I didn't find a solution for this problem with any DAX function. With the percentile I want to sort the attributes from highest to lowest according to the value of the [NPS_NPS] measure, and indicate the percentage of attributes that have the same or a lower NPS value.

I have not found a DAX formula with which to make this calculation, so I made mi DAX script. In the script I have 3 parts:

-->the ranking:  CALCULATE(RANKX(ALLSELECTED(dim_field01),CALCULATE([NPS_NPS],ALLEXCEPT(dim_field01,dim_field01[FIELD])),,DESC,Skip))

--> the number of ranking positions (I don't know how to calculate this value)

--> The percentile:

Percentile= CALCULATE(1-(DIVIDE((([Ranking]-0.5),/*Number ranking positions*/))))

So I have "2 problems", the first is that I don't know if  there is any DAX function for this kind of percentile

and the second is that I  also don't know any way to calcule the number or ranking positions.

Announcements

#### Check it out!

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

#### Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

#### The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors