Forum Discussion
Difference between MIN and current values based on filter of another column
The dataset looks something like this and is only downloaded for a given month:
| data.energy | location.circuit | received |
| 24026 | total | 10/08/2019 00:59 |
| 23906 | total | 09/08/2019 21:50 |
| 22589 | total | 09/08/2019 04:45 |
| 10258 | socket | 10/08/2019 00:30 |
| 10134 | socket | 09/08/2019 13:56 |
| 9945 | socket | 07/08/2019 23:34 |
| 2458 | light | 09/08/2019 14:45 |
| 2223 | light | 05/08/2019 23:47 |
| 2046 | light | 02/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.energy | location.circuit | received |
| 2026 | total | 10/08/2019 00:59 |
| 1906 | total | 09/08/2019 21:50 |
| 589 | total | 09/08/2019 04:45 |
| 358 | socket | 10/08/2019 00:30 |
| 234 | socket | 09/08/2019 13:56 |
| 45 | socket | 07/08/2019 23:34 |
| 458 | light | 09/08/2019 14:45 |
| 223 | light | 05/08/2019 23:47 |
| 46 | light | 02/08/2019 15:29 |
in that case, modify the code to this
EnergyUsed2 = VAR minVal = CALCULATE ( MIN ( 'Table'[data.energy] ); FILTER ( ALL ( 'Table' ); 'Table'[location.circuit] = MIN ( 'Table'[location.circuit] ) ) ) RETURN SUMX ( 'Table'; 'Table'[data.energy] - minVal )assuming this is the desired output
3 Replies
- sturlaws
Resident Rockstar
Hi, Anonymous
you can try this piece of DAX:EnergyUsed = VAR minVal = CALCULATE ( MIN ( 'Table'[data.energy] ); ALL ( 'Table'[receivedTime] ); FILTER ( ALL ( 'vDate' ); vDate[Month] = MIN ( vDate[Month] ) ) ) RETURN SUMX ( 'Table'; 'Table'[data.energy] - minVal )I have added a date table to your sample data, here is how the sample report I created looks: pbix
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.Hi sturlaws ,
Thank you for your suggestion. Unfortunately it didn't seem to have any effect, i.e. the EnergyUsed values are exactly the same as the original data.energy values. Also, it doesn't need to be filtered by month (or time, I believe), as I only download data for one month. I need the mininum from the column data.energy for a given location.circuit (e.g. 22000 for "total" ), and then substract that minimum for the other values for the same location.circuit.
- sturlaws
in that case, modify the code to this
EnergyUsed2 = VAR minVal = CALCULATE ( MIN ( 'Table'[data.energy] ); FILTER ( ALL ( 'Table' ); 'Table'[location.circuit] = MIN ( 'Table'[location.circuit] ) ) ) RETURN SUMX ( 'Table'; 'Table'[data.energy] - minVal )assuming this is the desired output