Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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]) )
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]) )
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.
What do you want to put in the 'Meter' column?
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]) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |