Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    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]) 
                  )

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        The value of master meter,

         

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

         

        Thank you so much!