Forum Discussion
Anonymous
8 years agoNot applicable
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...
- 8 years ago
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]) )
Anonymous
8 years agoNot applicable
| 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 |
Phil_Seamark
Microsoft Employee
8 years agoPlease 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])
)