Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am quite new to Power Bi and need some help with a DAX measure.
I have a table that contains charging cables and the amount of time they have been used. I would like to calculate the capacity utilization, i.e. how much percentage of the total time have the cables been used in average.
The cables are grouped and cannot be used all at the same time. The variable max_concurrent tells us how many of them can be used. Furthermore, cable c2-1 has not been there at 0:00-01:00, just at 01:00-02:00.
Think of tables like this:
charge_time:
Cable | timestamp | charge_time_sec |
C1-1 | 0:00-1:00 | 1800 |
C1-2 | 0:00-1:00 | 900 |
C1-3 | 0:00-1:00 | 0 |
C1-1 | 1:00-2:00 | 3600 |
C1-2 | 1:00-2:00 | 0 |
C1-3 | 1:00-2:00 | 2700 |
C2-1 | 1:00-2:00 | 450 |
metadata
cable | group | max_concurrent |
C1-1 | C1 | 2 |
C1-2 | C1 | 2 |
C1-3 | C1 | 2 |
C2-1 | C2 | 1 |
Calculations:
0:00-01:00: (1800+900+0)/(2*3600) = 37.5 %
1:00-2:00: (3600+0+2700+450)/(3*3600) = 62.5 %
Average: 50 %
I wrote the following measure that is working but slow.
caputil avg = var _table=SUMMARIZE(
charge_time,
charge_time[timestamp],
"caputil",
DIVIDE(SUM([charge_time_sec]),3600*[max concurrent cables])
) return
CALCULATE(AVERAGEX(_table,[caputil]))
max concurrent cables = SUMX(Distinct(metadata[group]), [max concurrent group])
max concurrent group = MAX(metadata[max_concurrent])
I would be really happy about any advice 😊 Thanks!
@Anna-Lena , Try a measure like
divide(Sum(Table[charge_time_sec]),distinctcount(Table[Cable])*distinctcount(Table[timestamp])*3600)
Hi,
thanks for the answer, but it is not working for my task.
That would work if all the cables could be used at the same time.
But as you see in my calculations, I would like to divide by 2*3600 at the first timestamp and 3*3600 at the second timestamp...