Forum Discussion
Sum Column exclude Rows
example data: Location EnergyConsumed StartTime Meter road 2 5/29/2018 13:00 0 dock 12 5/29/2018 13:00 0 bridge 2 5/29/2018 13:00 0 Master Meter 15 5/29/2018 13:00 1...
Please try the following calculated table. I have attached a PBIX file with the code inside :)
Table = VAR BaseTable = SUMMARIZECOLUMNS( HourlyEnergyData[StartTime], FILTER('HourlyEnergyData','HourlyEnergyData'[Meter]=0) , "Total", SUM(HourlyEnergyData[EnergyConsumed]) ) RETURN ADDCOLUMNS( BaseTable, "Meter",MINX( FILTER( 'HourlyEnergyData', 'HourlyEnergyData'[Meter]=1 && 'HourlyEnergyData'[StartTime] = EARLIER('HourlyEnergyData'[StartTime]) ), 'HourlyEnergyData'[EnergyConsumed]) )
The value of master meter,
ie. column of summed locations and column of master meter.
Thank you so much!
So based on the table you provided in the sample data. What would the expected value be?
Total Master Meter Date 14 15 5/29/2018 13:00 13 14 5/29/2018 14:00 9 10 5/29/2018 15:00 Please try the following calculated table. I have attached a PBIX file with the code inside :)
Table = VAR BaseTable = SUMMARIZECOLUMNS( HourlyEnergyData[StartTime], FILTER('HourlyEnergyData','HourlyEnergyData'[Meter]=0) , "Total", SUM(HourlyEnergyData[EnergyConsumed]) ) RETURN ADDCOLUMNS( BaseTable, "Meter",MINX( FILTER( 'HourlyEnergyData', 'HourlyEnergyData'[Meter]=1 && 'HourlyEnergyData'[StartTime] = EARLIER('HourlyEnergyData'[StartTime]) ), 'HourlyEnergyData'[EnergyConsumed]) )