Seeking Help to Calculate Percentiles Using Alternative DAX Formul

[Anonymous]

Hello community,

I hope this message finds you well. I am currently working on a project where I need to calculate percentiles in a slightly different way from the classic DAX formula. I have a DAX code snippet that calculates percentiles in my Power BI project, but it doesnt seem to work.

Here's the existing DAX code that I'm using:

TempoCoda90 =
CALCULATETABLE(
ADDCOLUMNS(
'Security_Percentili',
"Tempo_Coda_Ordinati", 'Security_Percentili'[Tempo_Coda],
"ID", RANKX(ALL('Security_Percentili'), 'Security_Percentili'[Tempo_Coda], , ASC, Dense)
),
ALL('Security_Percentili'[Tempo_Coda])
)

Numero_Record_Totali = COUNTROWS('Security_Percentili'[Tempo_Coda])

Indice_90_Percentile = ROUNDUP([Numero_Record_Totali] * 0.9, 0)

Valore_90_Percentile =
CALCULATE(
VALUES('Security_Percentili'[Tempo_Coda]),
FILTER('Tempo_Coda_Ordinati', 'Tempo_Coda_Ordinati'[ID] = [Indice_90_Percentile])
) / 60

[Anonymous]

Hi @Anonymous ,
Based on the provided DAX code, it seems you are trying to compute the 90th percentile of the Tempo_Coda column in the Security_Percentili table. Your approach involves ranking the values, determining the total count, calculating the index for the 90th percentile, and then extracting the corresponding value.
You can consider using the PERCENTILEX.INC or PERCENTILEX.EXC functions, which allow you to calculate percentiles over a table expression.
Here's an example of calculating the 90th percentile using PERCENTILEX.INC:

Valore_90_Percentile = 
PERCENTILEX.INC(
    ALL('Security_Percentili'[Tempo_Coda]), 
    'Security_Percentili'[Tempo_Coda],
    0.9
)

For more in-depth guidance on percentile functions in DAX, you can refer to the official Power BI documentation on PERCENTILEX.INC: 
PERCENTILEX.INC function (DAX) - DAX | Microsoft Learn

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly