Need to remove values from dynamic table in DAX percentile Calc

[JMG241188]

Hey,

I have a fomula that is calcualting the percentile of a value, however, I need to remove any row that is equal to "0" as these results should not get a percentile rank and are skewing the rating of the other values.

Any help would be greatly appreciated, I'm guessing there needs to be another filter where the Employees[Total Handled Rate 2022] <> 0 but can't seem to figure it out.

Formula is below;

Total Handled Rate 2022 Percentile =

VAR Adj2022 =

    MIN(Employees[Total Handled Rate 2022])

RETURN

    IF(

        HASONEVALUE(Employees[Code]),

        COALESCE(

            DIVIDE(

                CALCULATE(

                    COUNTROWS(Employees),

                    FILTER(

                        ALLEXCEPT(Employees,Employees[Department]),

                        Employees[Total Handled Rate 2022] > Adj2022

                    )

                ),

                CALCULATE(

                    COUNTROWS(Employees),

                    ALLEXCEPT(Employees,Employees[Department])

                )

            ),

            0

        )

    )

Thanks

[FreemanZ · Accepted solution]

hi @JMG241188 

try like:

Total Handled Rate 2022 Percentile =

VAR Adj2022 =

    MIN(Employees[Total Handled Rate 2022])

RETURN

    IF(

        HASONEVALUE(Employees[Code]),

        COALESCE(

            DIVIDE(

                CALCULATE(

                    COUNTROWS(Employees),

                    FILTER(

                        ALLEXCEPT(Employees,Employees[Department]),

                        Employees[Total Handled Rate 2022] > Adj2022&&Employees[Total Handled Rate 2022] <> 0

                    ),

                ),

                CALCULATE(

                    COUNTROWS(Employees),

                    ALLEXCEPT(Employees,Employees[Department]),

Employees[Total Handled Rate 2022] <> 0

                )

            ),

            0

        )

    )

[JMG241188]

Hey, thanks for the response, I'm getting an error of;

"Argument '3' in CALCULATE function is required.

[JMG241188]

just had to remove the comma after the bracket, thanks for your help