Sum of averages: why is my code wrong?

[Hank_]

Hello everybody!

I have read some posts in the forum and I got my sum of averages working. But I noticed that when I am computing the sum of the averages, this computes also the zeros. My code is wrong but I cannot find out why.

My approach may not be the best, so any help will be very appreciated. I am calculating step duration as a calculated column, then a measure for its average, and the sum of the averages through a group_by.

I attach the file with measures and descriptions of the issue.

sum of averages 

easyupload link 

Thanks a lot for any suggestion

[Hank_ · Accepted solution]

I solved with NOT and ISBLANK, in this way:

step duration avg measure = SUM(data[step duration]) / CALCULATE ( DISTINCTCOUNT ( data[# project], NOT( ISBLANK(data[step duration]].

And then reused my group by measure.

Thanks everybody for the hints! 🙂 

[v-xiaotang]

Hi @Hank_ 

You can take steps below for reference.

Create a measure:

TEST step duration avg =
CALCULATE (
    SUM ( dati[step duration] ) / DISTINCTCOUNT ( dati[# project] ),
    dati[step duration] <> 0
)

Then, you will get the following:

Hope it helps.

Best Regards,

Community Support Team _ Tang

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

[Hank_]

Hello @v-xiaotang , thank you. Exluding the empty steps was my first attempt.

This measures doesn't calculate the sum of the averages, like the measure 4 made with group by.

One way to make it work also for phase 4, which has an empty duration, is to add some condition on the measure 4, but for now I cannot find out. 

[v-xiaotang]

Hi @Hank_ 

Still not clear about the result you want, can you mark the value you want on the picture like this?

Besides, you said "this computes also the zeros", so what do you want to exlude? Empty (which filtered by function Blank())? Or zero (which filtered by <>0)? Or both?

Best Regards,

Community Support Team _ Tang

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

[Fowmy]

@Hank_ 

Not quite clear about the expected results but if you want to exclude the zero in your average measure or the Group By measure, you filter out the zeros as follows:

3_avg (OK for step 4; NO for step 1)) = 
CALCULATE(
    SUMX(
        VALUES(dati[Step]),
        AVERAGE(dati[step duration])
    ),
    dati[step duration] <> 0 
)

[Hank_]

Hi @Fowmy ! Thanks for the hint! This measure calculates correctly the 110.5, but computes 18 instead of 11.1 which is the correct sum of averages for phase 1 😕