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.

Thank you for your support

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.