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!
