Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Hank_
Helper I
Helper I

Sum of averages: why is my code wrong?

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

 

1 ACCEPTED SOLUTION
Hank_
Helper I
Helper I

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! 🙂 

View solution in original post

6 REPLIES 6
Hank_
Helper I
Helper I

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
Community Support
Community Support

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:

v-xiaotang_3-1619513850453.png

v-xiaotang_4-1619513905657.png

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.

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. 

Hi @Hank_ 

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

v-xiaotang_0-1619770355155.pngv-xiaotang_1-1619770551357.png

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
Super User
Super User

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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 😕 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors