How to calculate percentile

[Cevola]

Hi,

I need to create a percentile column based on the VALUE column. The percentile needs to be calculated only for some IDs like A1, A3, A7, A8 etc. The data i have is:

Id	Value	Percentile
A1	2.993857962
A2	4.790980789
A3	5.285423474
A4	9.845946214
A5	13.1619472
A6	3.061712719
A7	14.10853515
A8	20.40455305
A9	8.082005991
A10	0.927973851
A11	9.357774268
A12	12.00309514

I am using this measure but I am not getting the required output

Percentile =

VAR TotalVal =

    [Value Total]

RETURN

    IF(

        HASONEVALUE(Data[ID]),

        COALESCE(

            DIVIDE(

                CALCULATE(

                    COUNTROWS(Data),

                    FILTER(

                        ALLEXCEPT(Data,Data[ID]),

                        Data[Value] < TotalVal

                ),

                CALCULATE(

                    COUNTROWS(Data),

                    ALLEXCEPT(Data,Data[ID])

                )

            ),

            0

        )

    )

Please help

[v-zhangti]

Hi, @Cevola 

Based on the data you provided, what kind of output do you expect?

Reference can be made to such formulas.

PERCENTILE.EXC function (DAX) - DAX | Microsoft Learn

PERCENTILE.INC function (DAX) - DAX | Microsoft Learn

Best Regards,

Community Support Team _Charlotte

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