Search code examples
powerbivega-litedeneb

Show negative values in Deneb column chart in red

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.

Deneb screenshot With Neg field

Solution

  • 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