Split positive and negative values into two separate fields

I am using Grafana v10.0.3 with InfluxDB 1.8 and I would like to split up an energy meter kWh field, which has positive and negative values (import/export), in two separate fields (one for positive values, the other one for negative ones) that I can show the sum of all imported and all exported energy.

The total field is calculated by the sum of multiple import and export meters.

I thought it might work to make the field twice (total_pos and total_neg) and just filter it then, but it looks the calulcated values (min, max, average, total) don’t care about the filter.
Is this the way how it should be?

Is there another way to achieve that?
Maybe with an conditional expression which only calculate positive values in the total_pos field?

What data source is it? Influx mysql?

InfluxDB 1.8. is the datasource.

1 Like

Both the total_pos and total_neg are identical?

Correct, i duplicated the total counter.

So how can one ever be different than the other ?

My idea was to filter at one the positive values and on the others the negativ ones.

I would go with 2 separate queries. One for - the other for +

And then join thrm

Please provide some inline sample csv or line protocol data

So far as I understand is this not possible, because the total which I calculate depends on the sum of all meter (Z).
For this reason was it my idea to duplicate the total into total_negativ and total_positive and filter these values then somehow, ether in the expression or after it.

For example Z1_DLV (delivered) could be +10 kW while Z3_DRW (drawn) could be at the same time -2 kW, which would result in +8 kWh in total.

How can I do this?

This is how the energy meters (Z) are connected to each others and I want to calulate its total within 15 minutes.
PV Energy Meters

Z = energy meters (separate positive/negative kWh counters)
G = generator which represents the energy producer (which is actually a photovoltaic inverter)
M = motor which represents the energy consumers.

Each meter accumulates positive as well as negative values, but within 15 minutes all positive and negative values compensate each other and I want to show that in the total calculation which works so far, since I just have to make the drawn values negative and build then a sum of all meters.

This sum I want to split up in a positive and negative field that I can create a min,max, average and total calculation from the drawn as well as delivered energy.

Ga1,2023-07-30 12:00:00,33.5

Or export it from localhost:8086, open in notepad copy content and paste here

InfluxDB 1.8 has so far as I understand no web interface available.

I have exported some CSV data as well as .json data from the panel, does this maybe help?

1 Like

I am not going to click on some random url

Please post content as asked

Since I am not able to upload .txt or .csv files here, I just renamed the .csv files to .json files.
I did export this data by the software ioBroker which is also recording the data (not sure how else I could get the data), hope this is the data for wich are you looking for.

mqtt-client.0.EMTR.DG.DRW.W.json (18.0 KB)
mqtt-client.0.WR.WR2.Meter.ReverseActiveEnergy.json (16.6 KB)
mqtt-client.0.WR.WR2.Meter.PositiveActiveEnergy.json (16.6 KB)
mqtt-client.0.WR.WR1.Meter.ReverseActiveEnergy.json (16.6 KB)
mqtt-client.0.WR.WR1.Meter.PositiveActiveEnergy.json (16.6 KB)

1 Like
Ga1,2023-07-30 12:00:00,33.5

Already indicated how you can post inline data here

Just open the csv file you have linked with notepad select all copy and then paste the content of the csv here. Not the file because none of us can load any file in this forum

ok now we see your data posted to this forum. Do you care explaining what each represent before we ingest it into our local influxdb? DG.DRW.W, ReverseActive vs PositiveActive.

Sure, please see as followed:

DLV = delivery (positive values)
DRW = draw (negative values)
All these are kWh counters which increase all the time and for that reason spread() is used to get the consumed/delivered kWh in a certain time frame.


Z1_DRW (gets converted to negative):


Z2_DRW (gets converted to negative):

Z3_DRW (gets converted to negative):

You can also see the the queries and conversations I have used there:

I added some .json files which I exported from the panel, maybe these help as well:
Datafame.json (440.3 KB)
Panel Data.json (494.4 KB)
Panel.json (9.7 KB)

1 Like

And now please provide the schema of your table or tables where you ssve these data points

@michaeluray I tried this out with some dummy sample data with the TestData DB datasource.

Here are positive and negative values in each series:

Then I created a transform in the transform tab similar to yours:

In my example, the transform looks like it is only using positive or negative values in the legend calculations. Are you able to use the Panel>More>Get Help option to share a copy of your panel with randomized data that we can look at? I’m wondering if it’s specific to the datasource, visualization, or if there is some other dashboard setting that’s causing the result you’re seeing. Have you tried creating this panel from scratch in a new dashboard?


If you have already solved your challenge, just ignore me… I’m late in picking this up. This is what I have done:

Because I consume on 3 phases and produce on a single phase, the production is what I sent into the grid on that phase where the converters are. My queries on the dashboard are like this:

The InfluxDB side:

> show series from actual
> show field keys from actual
name: actual
fieldKey fieldType
-------- ---------
consumed float
produced float
> select * from actual where time > now() - 15s
name: actual
time                consumed phase produced
----                -------- ----- --------
1691133995509944118 0.109    1     
1691133995509944118          3     0.788
1691133995509944118 0.268    2     
1691134005534068594          3     0.75
1691134005534068594 0.262    2     
1691134005534068594 0.109    1
1 Like