topic Need to remove values from dynamic table in DAX percentile Calc in DAX Commands and Tips

Need to remove values from dynamic table in DAX percentile Calc

JMG241188 — Thu, 19 Jan 2023 18:38:34 GMT

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])

)

Thanks

Re: Need to remove values from dynamic table in DAX percentile Calc

FreemanZ — Thu, 19 Jan 2023 22:31:30 GMT

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

)

Re: Need to remove values from dynamic table in DAX percentile Calc

JMG241188 — Fri, 20 Jan 2023 00:42:21 GMT

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

"Argument '3' in CALCULATE function is required.

Re: Need to remove values from dynamic table in DAX percentile Calc

JMG241188 — Fri, 20 Jan 2023 00:51:20 GMT

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