Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to Solution.
@Anonymous,
See code below.
SUMX ( VALUES ( Table1[Script] ), CALCULATE ( AVERAGE ( Table1[ReferenceTime] ) ) )
@Anonymous
Try this modification
= IF ( HASONEVALUE ( Loops[RefTimeInSec] ), AVERAGE ( Loops[RefTimeInSec] ), SUMX ( VALUES ( Loops[RefTimeInSec] ), CALCULATE ( AVERAGE ( Loops[RefTimeInSec] ) ) ) )
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?
@Anonymous,
See code below.
SUMX ( VALUES ( Table1[Script] ), CALCULATE ( AVERAGE ( Table1[ReferenceTime] ) ) )
**bleep** you rule! It worked perfectly!
Thanks mate.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |