Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.