Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Difference between MIN and current value for filtered incremental values

The dataset looks something like this and is only downloaded for a given month:

data.energylocation.circuitreceived
24026total10/08/2019 00:59
23906total09/08/2019 21:50
22589total09/08/2019 04:45
10258socket10/08/2019 00:30
10134socket09/08/2019 13:56
9945socket07/08/2019 23:34
2458light09/08/2019 14:45
2223light05/08/2019 23:47
2046light02/08/2019 15:29

I am interested in the energy consumption per circuit for that month. As the energy values constantly increase, I would like to calculate the difference with the MIN for that month at each point in time (i.e. keep it incremental, but remove the offset). As the energy data mixes all the different circuits together, I need to figure out the MIN for each circuit and substract accordingly (presumably using filters).
I tried something like the below, but to no success:

 

 

EnergyUsed = 
CALCULATE(
    SUM(August[data.energy]),
    FILTER(
        ALLEXCEPT(August, August[location.circuit]),
        August[data.energy] = MIN(August[data.energy])
    )
)

 

 

So assuming the MIN for total was 22000, for socket the MIN was 9900 and for light the MIN was 2000, I would expect the results to look like this:

data.energylocation.circuitreceived
2026total10/08/2019 00:59
1906total09/08/2019 21:50
589total09/08/2019 04:45
358socket10/08/2019 00:30
234socket09/08/2019 13:56
45socket07/08/2019 23:34
458light09/08/2019 14:45
223light05/08/2019 23:47
46light02/08/2019 15:29

2 Replies