Show negative values in Deneb column chart in red

Question

I've been playing around with the Deneb visual in PowerBI Desktop and (amongst many other things) have been trying to create a simple column chart that shows negative values in red and positive values in green, however can't for the life of me seem to get it working - I believe the condition/test in my script is correct, but it refuses to 'fire' when it's 'true'

I've read through the condition page of the Vega-Lite documentation https://vega.github.io/vega-lite/docs/condition.html and have a condition section within the encoding/color

I've added Month End and MonthYear columns from my Calendar table and an EBITDA measure from a fact table to the Deneb visual

Month End	MonthYear	EBITDA
31/7/2021	"Jul-21"	8277.56
31/8/2021	"Aug-21"	-15123.66
30/9/2021	"Sep-21"	9502.11
31/10/2021	"Oct-21"	13090.99

{
  "data": {"name": "dataset"},
  "mark": "bar",
  "encoding": {
    "x": {
      "field": "MonthYear",
      "sort": {"field": "Month End"}
    },
    "y": {
      "field": "EBITDA",
      "aggregate": "sum"
    },
    "color": {
      "condition": {
        "test": "datum['EBITDA']<0",
        "value": "red"
      },
      "value": "green"
    }
  }
}

If I adjust the condition to be "test": "1==1" then the 'true' path works, so I assume I've got something wrong with my test line, though this seems to be correct per a lot of blogs, stackoverflow questions etc.

I've also tried using a "tranform:" channel to create a new Neg field in the Deneb dataset and referring to that field in my test, but it still won't adjust the colour.

score 0 · Accepted Answer · answered Feb 14 '23 at 00:59

It doesn’t like your aggregation. It looks like the data you are sending in is already aggregated by Power BI. If so, this will work:

    "y": {
      "field": "b",
      "type": "quantitative"
    },

View sample in the Vega Editor

If your data isn’t aggregated, add an aggregate transform like this:

  "transform": [
    {"aggregate": [{
       "op": "sum",
       "field": "b",
       "as": "bsum"
      }],
      "groupby": ["a"]}
  ],
  "mark": "bar",
  "encoding": {
    "x": {
      "field": "a",
      "sort": {"field": "a"}
    },
    "y": {
      "field": "bsum",
      "type": "quantitative"
    },
    "color": {
      "condition": {
        "test": "datum['bsum']<0",
        "value": "red"
      },
      "value": "green"
    }
  }
}

Open the Chart in the Vega Editor

ahh brilliant, thankyou, the removal of the aggregate and changing the type to quantitative solve it — Ben, Feb 14 '23 at 01:29

Show negative values in Deneb column chart in red

1 Answers1