cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Sum of Averages

Hello world!~

 

I've been trying to make a sum of averages for a few days and I think I am out of idea ... so I could use some help.

 

Let's say I have this table: 

 

ScriptDateReferenceTimeExecutionTime
Script15 hours ago13x
Script25 hours ago12x
Script35 hours ago11x
Script14 hours ago13x
Script24 hours ago12x
Script34 hours ago11x
Script13 hours ago13x
Script23 hours ago12x
Script33 hours ago11x
Script12 hours ago13x
Script22 hours ago12x
Script32 hours ago11x
Script11 hour ago13x
Script21 hour ago12x
Script31 hour ago11x
Script1now13x
Script2now12x
Script3now11x
Script1now13x
Script2now12x
Script3now11x

 

What I would like to have is a "stacked column chart & line" with my Script's execution time as stacked column grouped per hours with the average reference time for all 3 scripts as line ; just like that (here is an example, the data above isn't here represented) :Capture.PNG

Here, the line is calculated by making an average of all of my reference times which is not what I am looking for. I want it to average Script1's Reference time, same for script2 and script3 SEPARATED and THEN sum up the 3 of them. So following the example above, for the "now" date, my line should be (((13+ 13) / 2) + ((12 + 12) / 2) + ((11 + 11) / 2)) = 36 seconds.

 

Keep in mind that the number of script and the number of script execution per hours can change at any time

Until here I got this DAX measure but it gives me a "dot" and gives the exact same result Smiley Sad:

 

SumOfAvgRefTime = 
VAR AvgRefTime = AVERAGE(Loops[RefTimeInSec])

RETURN
IF( HASONEVALUE(Loops[RefTimeInSec]),
    AvgRefTime,
        SUMX( VALUES(Loops[RefTimeInSec]), AvgRefTime))

 Do you guys now how I could do it?

 

Thanks!

(Please try to use simple words, I'm not english)

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Sum of Average

@Anonymous,

 

See code below.

SUMX (
    VALUES ( Table1[Script] ),
    CALCULATE ( AVERAGE ( Table1[ReferenceTime] ) )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: Sum of Average

@Anonymous

 

Try this modification

 

=
IF (
    HASONEVALUE ( Loops[RefTimeInSec] ),
    AVERAGE ( Loops[RefTimeInSec] ),
    SUMX (
        VALUES ( Loops[RefTimeInSec] ),
        CALCULATE ( AVERAGE ( Loops[RefTimeInSec] ) )
    )
)
Try my new Power BI game Cross the River
Highlighted
Anonymous
Not applicable

Re: Sum of Average

Hey there,

 

Thanks for answering. The measure you gave me didn't really work :Capture.PNG

It's better at least but for this graph, I know that the line should be a163 seconds-constant line . But I see where you were going with it... I'll keep looking for it ; if someone got another idea?

Highlighted
Community Support
Community Support

Re: Sum of Average

@Anonymous,

 

See code below.

SUMX (
    VALUES ( Table1[Script] ),
    CALCULATE ( AVERAGE ( Table1[ReferenceTime] ) )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Sum of Average

**bleep** you rule! It worked perfectly! Smiley Very HappyHeart

Thanks mate.Capture.PNG

 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors