I’m no stranger to pivottables. I manage an IT helpdesk so part of my job is to take metric reports and analyze the data and pivottables are really great for quickly filtering and visualizing data.
I’m not even wanting to make anything very complex. I just want to track my savings since getting solar. So a spreadsheet that says I would have paid X this month, but instead I paid Y, so the savings is Z. I’ll probably just make each month a new sheet and create a summary dashboard sheet. Since I don’t need very granular data for this, I just exported Sep 1 - Oct 1 with the day interval.
But the way some of the data is organized in the export has me a little confused. I created a simple pivot table that shows name in the rows, and sum of kWh and average wattage is the columns and immediately noticed a few things.
- Total usage for each day is recorded as a device. This made my total usage for the month a lot higher than it actually was. But, once noticed this is no big deal to filter out. But this is where I notice some oddities.
- The device “total usage” says 1155 kWh. When looking at the web trends section for that month it says 1123 kWh. I went back to the table of data, and the export included data from 10/1, so I just removed that data. When I’m looking at a particular month in trends and then export that data, I expect it to only have that month. When doing the export, it does say 9/1 - 10/1 as the data range, but you can’t change this so I had assumed it would stop at 12:00am 10/1.
- Data from 10/1 has been removed from the table. Now in the pivottable, total usage shows 1123 matching the web. Now in my pivottable, I filter out that device. But the pivottable is showing the grand total as 468 kWh. Why does the total off all my devices not add up to 1123? EDIT: I figured this one out. “Other” is not included as a device
The solar Production device data is just broken. The Avg Wattage and kWh columns shows as negative and has an apostrophe in front, so Excel thinks it’s a String and not a number.
This is unrelated to the data I actually care about, but why is Avg Wattage calculated as kWh / 24 and not kWh / device runtime? My water heater shows as having an avg wattage of 143W. This makes the column pretty useless imo.
I might run into more issues as I play with the data, but this is what I’ve noticed so far.