Using Excel365.
I have hourly energy data for a whole year that I'm using a python script to pull out of another software and export to xlsx.
I'm trying to get a pivot table that presents the value of each End-Use by Month with a filter on Fuel (only really care about electricity for this purpose) when the sum of all end uses is the max, but can't figure out a way to make it happen.
I have different energy end uses (Interior Lighting, Space Heating, Space Cooling, etc.) and I'd want the answer to be the value for an hour where the maximum simultaneous values occurs in each month.
For example Peak energy use is going to occur during the day, and during the day exterior lighting is going to be off. So I'd want the hour during each month that all Electric end-uses sum to the highest total, not the maximum of each end use in a given month added together.
So I'd like the table to be the value of each end use in the max hour and the "grand total value" should be that max concurrent value that occurs during the month.
I've attached 2 months of data.
Not sure I understand what exactly you are looking for, but perhaps that the PT in the attached file is a start. If not, please specify what "max concurrent value" should be shown based on your example data.
Added some context, yellow highlighted is basically to what I'm trying to get the pivot table to spit out as an answer.
I've looked at the files and the what I can tell you is the way I do that monthly calculation.
The start value is the value at the min date value of a particular month and the end value is the min date value of the next month. if thsi month is now present than the max date value of the month in question.
You can use these date values as filter for all the different meters and values.
What I have done in my own Excel files is add two helper columns, one for the year and one for the month (number)
Hopes it makes sense
I'm not a Python person, but by looking at your desired end result (the yellow area) I believe I understand what you are needing.
I connected to the data with Power Query and after some grouping, merging and expanding I was able to extract only the rows that attributed to the maximum hourly electricity usage in each month. Then I could pivot those within PQ en create a table that contains the result you need. I attached my file with this quite crude PQ solution.
Now I don't know if @Hans intended to add his file, but it didn't come through. I'm sure there are other ways to achieve your goal. Perhaps Hans' solution will work for you as well.
