Sum Column exclude Rows

[Anonymous]

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.

[Phil_Seamark]

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]) 
                    )

[Anonymous]

Yes that worked to how it was specified.

However, can you modify it to also include a column "Meter Value"? the  FILTER('HourlyEnergyData','HourlyEnergyData'[Meter]=0) , restricts me from adding "Meter Value" in as another column.  

[Phil_Seamark]

What do you want to put in the 'Meter' column?

[Anonymous]

The value of master meter,

ie. column of summed locations and column of master meter.

Thank you so much!

[Phil_Seamark]

So based on the table you provided in the sample data.  What would the expected value be?

[Anonymous]

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 · Accepted solution]

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]) 
                  )