cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ivan_Ramos
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?

Thanks in advance

2 REPLIES 2
v-easonf-msft
Community Support
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

I have 2 tables with the following structure:

Ivan_Ramos_1-1628664632452.png

 

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.

Ivan_Ramos_2-1628665892331.png

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

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

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

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

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

June 20 episode 7 with aka link 768x460.jpg

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