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*/))))
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: