Excluding values in AVG not working

[valpassos]

Hi community,

I have a duration measure in days that tells me how much time a task took to be completed, in average, as follows:

AVG(Interval([Task Completed Date] - [Task Started Date])

But there are certain tasks that take less than half a day (<0.5 of duration) to be completed, and I want to exclude these tasks from my AVG duration. How can I accomplish that?

I have tried the following:

AVG({<%Task_Duration-={"<0.5"}>} %Task_Duration)

This is supposed to exclude tasks whose duration are less that 0.5 days, but it isn't working as expected - the AVG is still counting them.

What am I doing wrong?

Thank you for your help!,

Lisa

[dplr-rn]

The set analysis Syntax looks correct.

How are you creating the column %Task_Duration

I would check the value,type of that and double check

[valpassos]

Hi,

The %Task_Duration field is calculated in the model in this way:

Interval([Task Completed Date] - [Task Started Date])

I'm trying to show the AVG on a KPI, not a table.

Thanks,

Lisa

[dplr-rn]

thats fine.

check the data in column and column type.

i ran a test on the set analysis to make sure with some dummy data and it works

mine Avg({<Duration-={"<0.5"}>}Duration)

your AVG({<%Task_Duration-={"<0.5"}>} %Task_Duration)

so it must be something in data level

[valpassos]

If a put the expression in a table, with or without SA, the value is the same

Duration with SA: 

AVG({<%Task_Duration-={"<0.5"}>} %Task_Duration)

Duration without SA: 

AVG(%Task_Duration)

[valpassos]

Forgot the printscreen:

[dwforest]

set analysis is calculated once per chart (or table). NOT once per row, switch to IF
AVG(IF(%TaskDuration >0.5,%TaskDuration))

[dplr-rn]

your previous comment said "I'm trying to show the AVG on a KPI, not a table."

[valpassos]

I've put the expressions in the table just for testing purposes

[dplr-rn]

share your output in KPI. table a lot depends on the dimensions used etc.

And also include an extract of the duration column if possible