How to summarize 15 minute readings into Hourly readings.

[moltra]

I have a table the has readings every 15 minutes for each meter.  I want to create a table showing the sum of the four 15 minute readings for each hour.  I also need to have the area and meter name

Meter name	Area	Date	Ending Time	Hour	reading
Meter 1	area 1	3/1/2021	09:00	9	123
Meter 2	area 1	3/1/2021	09:00	9	343
Meter 3	area 2	3/1/2021	09:00	9	2342
Meter 1	area 1	3/1/2021	09:15	9	232
Meter 2	area 1	3/1/2021	09:15	9	456
Meter 3	area 2	3/1/2021	09:15	9	546
Meter 1	area 1	3/1/2021	09:30	9	143
Meter 2	area 1	3/1/2021	09:30	9	368
Meter 3	area 2	3/1/2021	09:30	9	554
Meter 1	area 1	3/1/2021	09:45	9	123
Meter 2	area 1	3/1/2021	09:45	9	343
Meter 3	area 2	3/1/2021	09:45	9	755
Meter 1	area 1	3/1/2021	10:00	10	545
Meter 2	area 1	3/1/2021	10:00	10	655
Meter 3	area 2	3/1/2021	10:00	10	458
Meter 1	area 1	3/1/2021	10:15	10	425
Meter 2	area 1	3/1/2021	10:15	10	457
Meter 3	area 2	3/1/2021	10:15	10	325

What I am looking to do is create a table with the following.

Meter name	Area	Date	Hour	reading
Meter 1	Area 1	3/1/2021	9	621
Meter 2	Area 1	3/1/2021	9	1510
Meter 3	Area 2	3/1/2021	9	4197
Meter 1	Area 1	3/1/2021	10	Sum hour 10
Meter 2	Area 1	3/1/2021	10	Sum hour 10
Meter 3	Area 2	3/1/2021	10	Sum hour 10

If possible I would like to add the ending time period for each 9:15 + 9:30 + 9:45 + 10:00 under the Hour 9 reading.  This way I get all the power usage for the 9 hour period.

[moltra]

I was looking for a table that I could view, but I can do the same thing with a visual.  

[BA_Pete · Accepted solution]

Hi @moltra ,

In Power query, you can add a new column like this:

pqNewHour =
if Time.Minute([endingTime]) = 0 then [hour] - 1 else [hour]

In DAX you can add a new column like this:

daxNewHour = 
SWITCH(
    MINUTE(yourTable[endingTime]),
    0, yourTable[hour] - 1,
    yourTable[hour]
)

Then just use your new column as the visual dimension.

This gives me the following output:

Pete

[moltra]

I am looking for the sum of the reading for each hour.  so I need to summarize the 4 readings for each hour and put it in a table.  

[BA_Pete]

Hi @moltra ,

I'm not sure I understand. The example I provided IS summing the readings for the four 15-minute segments within the new hour dimension. When the new hour dimensions are added to a visual with the readings, Power BI automatically aggregates the values.

From what I can see, my example output is the same as your required output table. The only difference is that the meter totals in mine are for 09:15, 09:30, 09:45, 10:00, as per your requirements here:

"If possible I would like to add the ending time period for each 9:15 + 9:30 + 9:45 + 10:00 under the Hour 9 reading."

Pete

[moltra]

Your Dax does take care of the hourly portion of the problem.  Can you show me the Dax that you used to get the rest of the table, mainly the sum of the readings.

I have gotten it to work in DAX Studio, but have not figured out how to do it in Power Bi.

[BA_Pete]

@moltra ,

There's no DAX creating the rest of the table in my example. The [reading] field is in number format so I just drag the field into the table and Power BI automatically aggregates it.

If you want to specifically calculate the sum rather than have Power BI auto-aggregate, you can use a measure like this, but it's not entirely necessary:

_sumOfReading = SUM(yourTable[reading])

I'm not sure whether you are working on something more complex than you have asked about here, but for just creating the output table as I have, you DO NOT need DAX Studio. You just need to drag the fields from your source table ([meterName], [area], [date], [reading]) into your report in Power BI Desktop, and also drag in your [daxNewHour] OR [pqNewHour] field and PBI will create the output that I provided.

Pete

[moltra]

Yes, I am working on something more complex.  I am trying to figure out why there is a slight difference between two reports pulling data from the same data source.  The difference is around 0.05%.

[BA_Pete]

@moltra ,

Fair enough.

Was there anything else you needed to help with your original question or does what I said make sense?

Pete

[moltra]

yes it makes since.  Thanks for your help