Generate new table with calculated rows based on daily MIN and MAX variables

[Anonymous]

I have an energy dataset and need to insert calculated rows (i.e. figure out the difference between the total and the other variables). I am using this approach: https://community.powerbi.com/t5/Desktop/Insert-custom-rows/m-p/731157#M352721 , i.e. generating a new table with rows and then merging those into the original table using the UNION function. My current solution is very ridgid and only gives me the calculated values for the first and last day of my dataset (which is one month). I would like to be able to see a higher resolution, i.e. calculate the "other" variable each day. I am not sure if I need to generate a row for each day or if it is possible to calculate it dynamically based on report level or other filters. Any suggestions? My current code looks like this:

custom rows = 
VAR minTotalVal =
    CALCULATE(
        MIN(September[data.energy]),
        FILTER(
            ALL(September),
            September[location.circuit] = "total"
        )
    )

VAR minSockets1Val =
    CALCULATE(
        MIN(September[data.energy]),
        FILTER(
            ALL(September),
            September[location.circuit] = "sockets1"
        )
    )

VAR minSockets2Val =
    CALCULATE(
        MIN(September[data.energy]),
        FILTER(
            ALL(September),
            September[location.circuit] = "sockets2"
        )
    )

VAR minSockets3Val =
    CALCULATE(
        MIN(September[data.energy]),
        FILTER(
            ALL(September),
            September[location.circuit] = "sockets3"
        )
    )

VAR minLights1Val =
    CALCULATE(
        MIN(September[data.energy]),
        FILTER(
            ALL(September),
            September[location.circuit] = "lights1"
        )
    )

VAR minLights2Val =
    CALCULATE(
        MIN(September[data.energy]),
        FILTER(
            ALL(September),
            September[location.circuit] = "lights2"
        )
    )

VAR maxTotalVal =
    CALCULATE(
        MAX(September[data.energy]),
        FILTER(
            ALL(September),
            September[location.circuit] = "total"
        )
    )

VAR maxSockets1Val =
    CALCULATE(
        MAX(September[data.energy]),
        FILTER(
            ALL(September),
            September[location.circuit] = "sockets1"
        )
    )

VAR maxSockets2Val =
    CALCULATE(
        MAX(September[data.energy]),
        FILTER(
            ALL(September),
            September[location.circuit] = "sockets2"
        )
    )

VAR maxSockets3Val =
    CALCULATE(
        MAX(September[data.energy]),
        FILTER(
            ALL(September),
            September[location.circuit] = "sockets3"
        )
    )

VAR maxLights1Val =
    CALCULATE(
        MAX(September[data.energy]),
        FILTER(
            ALL(September),
            September[location.circuit] = "lights1"
        )
    )

VAR maxLights2Val =
    CALCULATE(
        MAX(September[data.energy]),
        FILTER(
            ALL(September),
            September[location.circuit] = "lights2"
        )
    )

RETURN
    UNION(
        ROW(
            "accountId", LOOKUPVALUE(September[accountId],September[data.energy], minTotalVal),
            "DateInt", LOOKUPVALUE(September[DateInt],September[data.energy], minTotalVal),
            "received", LOOKUPVALUE(September[received],September[data.energy], minTotalVal),
            "TimeKey", LOOKUPVALUE(September[TimeKey],September[data.energy], minTotalVal),
            "data.energy", CALCULATE(
                minTotalVal - minSockets1Val - minSockets2Val - minSockets3Val - minLights1Val - minLights2Val
            ),
            "location.circuit", "others"
        ),
        ROW(
            "accountId", LOOKUPVALUE(September[accountId],September[data.energy], maxTotalVal),
            "DateInt", LOOKUPVALUE(September[DateInt],September[data.energy], maxTotalVal),
            "received", LOOKUPVALUE(September[received],September[data.energy], maxTotalVal),
            "TimeKey", LOOKUPVALUE(September[TimeKey],September[data.energy], maxTotalVal),
            "data.energy", CALCULATE(
                maxTotalVal - maxSockets1Val - maxSockets2Val - maxSockets3Val - maxLights1Val - maxLights2Val
            ),
            "location.circuit", "others"
        )
    )

[Anonymous · Accepted solution]

I found a nice workaround that works without having to add rows to my table, see here:
https://community.powerbi.com/t5/Desktop/Waterfall-Chart-using-multiple-measures/m-p/643163/highligh...