Sum of Averages

[Anonymous]

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: 

Script	Date	ReferenceTime	ExecutionTime
Script1	5 hours ago	13	x
Script2	5 hours ago	12	x
Script3	5 hours ago	11	x
Script1	4 hours ago	13	x
Script2	4 hours ago	12	x
Script3	4 hours ago	11	x
Script1	3 hours ago	13	x
Script2	3 hours ago	12	x
Script3	3 hours ago	11	x
Script1	2 hours ago	13	x
Script2	2 hours ago	12	x
Script3	2 hours ago	11	x
Script1	1 hour ago	13	x
Script2	1 hour ago	12	x
Script3	1 hour ago	11	x
Script1	now	13	x
Script2	now	12	x
Script3	now	11	x
Script1	now	13	x
Script2	now	12	x
Script3	now	11	x

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

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 :

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)

[Zubair_Muhammad]

@Anonymous

Try this modification

=
IF (
    HASONEVALUE ( Loops[RefTimeInSec] ),
    AVERAGE ( Loops[RefTimeInSec] ),
    SUMX (
        VALUES ( Loops[RefTimeInSec] ),
        CALCULATE ( AVERAGE ( Loops[RefTimeInSec] ) )
    )
)

[Anonymous]

Hey there,

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

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?

[v-chuncz-msft · Accepted solution]

@Anonymous,

See code below.

SUMX (
    VALUES ( Table1[Script] ),
    CALCULATE ( AVERAGE ( Table1[ReferenceTime] ) )
)

[Anonymous]

**bleep** you rule! It worked perfectly! 

Thanks mate.