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 |
| road | 3 | 5/29/2018 14:00 | 0 |
| dock | 4 | 5/29/2018 14:00 | 0 |
| bridge | 6 | 5/29/2018 14:00 | 0 |
| Master Meter | 14 | 5/29/2018 14:00 | 1 |
| road | 1 | 5/29/2018 15:00 | 0 |
| dock | 3 | 5/29/2018 15:00 | 0 |
| bridge | 5 | 5/29/2018 15:00 | 0 |
| Master Meter | 10 | 5/29/2018 15:00 | 1 |
I would like to sum EnergyConsumed like so,
SUMMARIZECOLUMNS(
HourlyEnergyData[StartTime],
"Total", SUM(HourlyEnergyData[EnergyConsumed]
)
that creates a new table with it that looks like this,
| Total | Date |
| 14 | 5/29/2018 13:00 |
| 13 | 5/29/2018 14:00 |
| 9 | 5/29/2018 15:00 |
but exclude "Master Meter" from the summarized sum, total.
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]) )
7 Replies
Replies have been turned off for this discussion
HI Anonymous
The SUMMARIZECOLUMNS function accepts a filter parameter as an argument so perhaps try this
Table = SUMMARIZECOLUMNS( HourlyEnergyData[StartTime], FILTER('HourlyEnergyData','HourlyEnergyData'[Meter]=0) , "Total", SUM(HourlyEnergyData[EnergyConsumed]) )What do you want to put in the 'Meter' column?