Changes to Energy Usage panel in Grafana

[youzer-name]

Edit: This post is no longer relevant as the work-around isn't needed under Grafana 9.1. There is an option to align the zero points of a time series graph in that version

---

I've been playing around with the Energy Usage panel in Grafana and though I'd share the changes I made in case anyone else finds them useful. The default panel uses the "Graph (old)" type, and that doesn't support setting a soft min/max for the Y axis, which is something I wanted to have. The soft min/max are available in the newer time series panel.

I like having the soft max/soft min because most of the time, the lines on the graph for my 8 kW solar and 2 powerwalls happily exist in the range of +/- 7 kW, often less. However when my cars charge my home use can go to ~ 10 kW. Last night I had my first storm watch event, and it overlapped with one of my cars charging, so my home use went a bit above 15 kW.

If I set hard max/min for +/- 15 kW, then most of the time the data would very compressed with a lot of blank space at the top and bottom. But if I set hard max/min for +/- 5 kW, then I lose any scale on the values that run off the top or bottom of the chart.

However when switching to time series you lose the ability to line up the left and right Y axes at 0, and the soft max/min will set different values for the max and min based on the data, causing the zero to not be vertically centered in the panel. That causes the zero point of the Sun and Moon curve to move away from the left Y axis zero and I didn't like the way that looked.

So my challenge was to find a way to use the soft min/max but still have the min and max equal when the soft values are exceeded. That will keep the zero point of the left Y axis in the center of the graph, lined up with the right axis.

After a bit of trial and error, I think I have this panel working the way I want. Here is what I changed to get there:

First, I clicked the button in the "Display" section to migrate the panel to Time series.

The I added a soft min and soft max of -5000 and 5000 to the Axis settings. I may adjust that later, but those values work for now.

I added overrides for Fields with name "Sun Altitude":

Then to deal with the problem of the positive and negative Y axis regions being different, I added queries like this:

(one is displayed in raw editor mode, one in visual editor mode, but they both do the same thing for two different fields).

Each query does a "math * -1" on the field. So far I have queries for "home", "from_grid", and "solar". I may have to add others later. I've only had my Powerwalls a month, so I'm not sure what my data will look like over the summer. Basically, I want an inverse value of any field that might be the largest value on the chart. So for example, if my "from_grid" hits 15 kW like it did last night, then this query will return -15 kW for "hidden_grid".

Each of these queries is aliased with "hidden_" in the name, I created a series override for fields with names like hidden that hide the inverse data points.

This has the effect of hiding the inverse values, but they still affect the Y axis max and min.

I also decided I'd rather have the charge level use the whole of the graph, not just the upper half, so I want my right axis to go from 0 to 100, not from -100 to 100. In other words, I want a 50% charge to line up with the zero on the left, using 50% of the whole height of the panel.

In the overrides for Fields with name "Charge" I set the standard options -> Min/Max to 0 and 100. If you wanted the default behavior of having the charge curve stay in the upper half of the graph, this would be set to -100 and 100.

The final result gives me charts like this for this morning where my peak value so far is below 5 kW:

and gives me this for yesterday when I had the storm watch event:

My panel JSON is below. My Data Source name is different from the project default, so if you want to import this you'll need to select the correct data sources in the query editor.

{
  "id": 30,
  "gridPos": {
    "h": 15,
    "w": 19,
    "x": 0,
    "y": 2
  },
  "type": "timeseries",
  "title": "Energy Usage",
  "datasource": {
    "type": "datasource",
    "uid": "-- Mixed --"
  },
  "pluginVersion": "8.4.2",
  "fieldConfig": {
    "defaults": {
      "custom": {
        "drawStyle": "line",
        "lineInterpolation": "linear",
        "barAlignment": 0,
        "lineWidth": 1,
        "fillOpacity": 30,
        "gradientMode": "none",
        "spanNulls": true,
        "showPoints": "never",
        "pointSize": 5,
        "stacking": {
          "mode": "none",
          "group": "A"
        },
        "axisPlacement": "auto",
        "axisLabel": "",
        "scaleDistribution": {
          "type": "linear"
        },
        "hideFrom": {
          "tooltip": false,
          "viz": false,
          "legend": false
        },
        "thresholdsStyle": {
          "mode": "off"
        },
        "axisSoftMax": 5000,
        "axisSoftMin": -5000
      },
      "color": {
        "mode": "palette-classic"
      },
      "mappings": [],
      "thresholds": {
        "mode": "absolute",
        "steps": [
          {
            "color": "green",
            "value": null
          }
        ]
      },
      "links": [],
      "unit": "watt"
    },
    "overrides": [
      {
        "matcher": {
          "id": "byName",
          "options": "Charge"
        },
        "properties": [
          {
            "id": "color",
            "value": {
              "fixedColor": "red",
              "mode": "fixed"
            }
          },
          {
            "id": "custom.fillOpacity",
            "value": 0
          },
          {
            "id": "custom.lineWidth",
            "value": 2
          },
          {
            "id": "custom.drawStyle",
            "value": "line"
          },
          {
            "id": "unit",
            "value": "percent"
          },
          {
            "id": "min",
            "value": 0
          },
          {
            "id": "max",
            "value": 100
          }
        ]
      },
      {
        "matcher": {
          "id": "byName",
          "options": "Grid Usage"
        },
        "properties": [
          {
            "id": "color",
            "value": {
              "fixedColor": "purple",
              "mode": "fixed"
            }
          }
        ]
      },
      {
        "matcher": {
          "id": "byName",
          "options": "Home Usage"
        },
        "properties": [
          {
            "id": "color",
            "value": {
              "fixedColor": "blue",
              "mode": "fixed"
            }
          }
        ]
      },
      {
        "matcher": {
          "id": "byName",
          "options": "Powerwall"
        },
        "properties": [
          {
            "id": "color",
            "value": {
              "fixedColor": "green",
              "mode": "fixed"
            }
          }
        ]
      },
      {
        "matcher": {
          "id": "byName",
          "options": "Solar Energy"
        },
        "properties": [
          {
            "id": "color",
            "value": {
              "fixedColor": "yellow",
              "mode": "fixed"
            }
          }
        ]
      },
      {
        "matcher": {
          "id": "byName",
          "options": "Sun altitude"
        },
        "properties": [
          {
            "id": "color",
            "value": {
              "fixedColor": "rgb(192, 192, 192)",
              "mode": "fixed"
            }
          },
          {
            "id": "unit",
            "value": "percent"
          },
          {
            "id": "min",
            "value": -100
          },
          {
            "id": "max",
            "value": 100
          },
          {
            "id": "custom.lineStyle",
            "value": {
              "fill": "dot",
              "dash": [
                0,
                10
              ]
            }
          },
          {
            "id": "custom.fillOpacity",
            "value": 10
          },
          {
            "id": "custom.axisPlacement",
            "value": "hidden"
          },
          {
            "id": "custom.hideFrom",
            "value": {
              "tooltip": true,
              "viz": false,
              "legend": true
            }
          }
        ]
      },
      {
        "matcher": {
          "id": "byRegexp",
          "options": "/.*hidden.*/"
        },
        "properties": [
          {
            "id": "custom.drawStyle",
            "value": "points"
          },
          {
            "id": "custom.pointSize",
            "value": 1
          },
          {
            "id": "custom.hideFrom",
            "value": {
              "legend": true,
              "tooltip": true,
              "viz": false
            }
          }
        ]
      }
    ]
  },
  "options": {
    "tooltip": {
      "mode": "multi",
      "sort": "none"
    },
    "legend": {
      "displayMode": "list",
      "placement": "bottom",
      "calcs": [
        "max"
      ]
    }
  },
  "targets": [
    {
      "alias": "Home Usage",
      "datasource": {
        "type": "influxdb",
        "uid": "I8KHPdzRk"
      },
      "groupBy": [
        {
          "params": [
            "1m"
          ],
          "type": "time"
        },
        {
          "params": [
            "none"
          ],
          "type": "fill"
        }
      ],
      "measurement": "http",
      "orderByTime": "ASC",
      "policy": "autogen",
      "query": "SELECT \"mean_load_instant_power\" FROM \"http\" WHERE $timeFilter tz('America/New_York')",
      "rawQuery": false,
      "refId": "A",
      "resultFormat": "time_series",
      "select": [
        [
          {
            "params": [
              "home"
            ],
            "type": "field"
          },
          {
            "params": [],
            "type": "mean"
          }
        ]
      ],
      "tags": [],
      "tz": "America/New_York"
    },
    {
      "alias": "Solar Energy",
      "datasource": {
        "type": "influxdb",
        "uid": "I8KHPdzRk"
      },
      "groupBy": [
        {
          "params": [
            "1m"
          ],
          "type": "time"
        },
        {
          "params": [
            "none"
          ],
          "type": "fill"
        }
      ],
      "hide": false,
      "measurement": "http",
      "orderByTime": "ASC",
      "policy": "autogen",
      "refId": "B",
      "resultFormat": "time_series",
      "select": [
        [
          {
            "params": [
              "solar"
            ],
            "type": "field"
          },
          {
            "params": [],
            "type": "mean"
          }
        ]
      ],
      "tags": [],
      "tz": "America/New_York"
    },
    {
      "alias": "Powerwall",
      "datasource": {
        "type": "influxdb",
        "uid": "I8KHPdzRk"
      },
      "groupBy": [
        {
          "params": [
            "5m"
          ],
          "type": "time"
        }
      ],
      "hide": false,
      "measurement": "http",
      "orderByTime": "ASC",
      "policy": "autogen",
      "query": "SELECT mean(from_pw) - mean(to_pw) FROM autogen.http WHERE $timeFilter GROUP BY time(1m) fill(none) tz('America/New_York')",
      "rawQuery": true,
      "refId": "C",
      "resultFormat": "time_series",
      "select": [
        [
          {
            "params": [
              "from_pw+to_pw"
            ],
            "type": "field"
          },
          {
            "params": [],
            "type": "mean"
          }
        ]
      ],
      "tags": [],
      "tz": "America/New_York"
    },
    {
      "alias": "Grid Usage",
      "datasource": {
        "type": "influxdb",
        "uid": "I8KHPdzRk"
      },
      "groupBy": [
        {
          "params": [
            "5m"
          ],
          "type": "time"
        },
        {
          "params": [
            "none"
          ],
          "type": "fill"
        }
      ],
      "hide": false,
      "measurement": "http",
      "orderByTime": "ASC",
      "policy": "default",
      "query": "SELECT mean(from_grid) - mean(to_grid) FROM autogen.http WHERE $timeFilter GROUP BY time(1m) fill(none) tz('America/New_York') ",
      "rawQuery": true,
      "refId": "D",
      "resultFormat": "time_series",
      "select": [
        [
          {
            "params": [
              "site_instant_power"
            ],
            "type": "field"
          },
          {
            "params": [],
            "type": "mean"
          }
        ]
      ],
      "tags": [],
      "tz": "America/New_York"
    },
    {
      "alias": "Charge",
      "datasource": {
        "type": "influxdb",
        "uid": "I8KHPdzRk"
      },
      "groupBy": [
        {
          "params": [
            "1m"
          ],
          "type": "time"
        },
        {
          "params": [
            "none"
          ],
          "type": "fill"
        }
      ],
      "hide": false,
      "measurement": "http",
      "orderByTime": "ASC",
      "policy": "autogen",
      "query": "SELECT \"mean_percentage\" FROM \"http\" WHERE $timeFilter",
      "rawQuery": false,
      "refId": "E",
      "resultFormat": "time_series",
      "select": [
        [
          {
            "params": [
              "percentage"
            ],
            "type": "field"
          },
          {
            "params": [],
            "type": "last"
          },
          {
            "params": [
              " /0.95 - 5/0.95"
            ],
            "type": "math"
          }
        ]
      ],
      "tags": [],
      "tz": "America/New_York"
    },
    {
      "datasource": {
        "type": "fetzerch-sunandmoon-datasource",
        "uid": "GgGN2DzRk"
      },
      "refId": "F",
      "target": "sun_altitude"
    },
    {
      "alias": "hidden_home",
      "datasource": {
        "uid": "I8KHPdzRk"
      },
      "groupBy": [
        {
          "params": [
            "$__interval"
          ],
          "type": "time"
        },
        {
          "params": [
            "none"
          ],
          "type": "fill"
        }
      ],
      "hide": false,
      "measurement": "http",
      "orderByTime": "ASC",
      "policy": "autogen",
      "query": "SELECT mean(\"home\") *-1 FROM \"autogen\".\"http\" WHERE $timeFilter GROUP BY time($__interval) fill(none)\n\n",
      "rawQuery": true,
      "refId": "G",
      "resultFormat": "time_series",
      "select": [
        [
          {
            "params": [
              "home"
            ],
            "type": "field"
          },
          {
            "params": [],
            "type": "mean"
          },
          {
            "params": [
              "*-1"
            ],
            "type": "math"
          }
        ]
      ],
      "tags": []
    },
    {
      "alias": "hidden_grid",
      "datasource": {
        "uid": "I8KHPdzRk"
      },
      "groupBy": [
        {
          "params": [
            "$__interval"
          ],
          "type": "time"
        },
        {
          "params": [
            "none"
          ],
          "type": "fill"
        }
      ],
      "hide": false,
      "measurement": "http",
      "orderByTime": "ASC",
      "policy": "autogen",
      "refId": "H",
      "resultFormat": "time_series",
      "select": [
        [
          {
            "params": [
              "from_grid"
            ],
            "type": "field"
          },
          {
            "params": [],
            "type": "mean"
          },
          {
            "params": [
              "*-1"
            ],
            "type": "math"
          }
        ]
      ],
      "tags": []
    },
    {
      "alias": "hidden_solar",
      "datasource": {
        "uid": "I8KHPdzRk"
      },
      "groupBy": [
        {
          "params": [
            "$__interval"
          ],
          "type": "time"
        },
        {
          "params": [
            "none"
          ],
          "type": "fill"
        }
      ],
      "hide": false,
      "measurement": "http",
      "orderByTime": "ASC",
      "policy": "autogen",
      "refId": "I",
      "resultFormat": "time_series",
      "select": [
        [
          {
            "params": [
              "solar"
            ],
            "type": "field"
          },
          {
            "params": [],
            "type": "mean"
          },
          {
            "params": [
              "*-1"
            ],
            "type": "math"
          }
        ]
      ],
      "tags": []
    }
  ],
  "transparent": true,
  "interval": ""
}

[jasonacox]

Nice work @youzer-name ! Thanks for posting this for the community.