Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jonespossibly
Advocate I
Advocate I

Another Deneb query - 2 data fields on one bar chart

I have cause to render two different data fields on to the same bars in the same chart.

so for instance the x axis will be date

y axis should have field 'A' and field 'B' stacked on top of each other for the given date.

Also both fields are grouped by another field, 'Z'.

Groupings for A should be shown separately from Groupings for B in the legend,

 

Getting A to work is simple:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {
    "values":[
      {"A":10, "B":20, "Z":"poop", "created":"2022/01/01"},
      {"A":10, "B":10, "Z":"moop", "created":"2022/01/01"},
      {"A":10, "B":5, "Z":"doop", "created":"2022/01/01"},
      {"A":13, "B":12, "Z":"poop", "created":"2022/02/01"},
      {"A":33, "B":10, "Z":"moop", "created":"2022/02/01"},
      {"A":0, "B":5, "Z":"doop", "created":"2022/02/01"},
      {"A":13, "B":5, "Z":"poop", "created":"2022/03/01"},
      {"A":3, "B":2, "Z":"moop", "created":"2022/03/01"},
      {"A":20, "B":7, "Z":"doop", "created":"2022/03/01"}
      ]
    },
  "mark": "bar",
  "encoding": {
    "x": {
      "timeUnit": "month",
      "field": "created",
      "type": "ordinal"
    },
    "y": {
      "field":"A",
      "type": "quantitative"
    },
    "color": {
      "field": "Z",
      "type": "nominal"
    }
  }
}

how would i go about adding the figures from 'B' onto each bar, in different colours and presented on the legend on separate rows?

1 ACCEPTED SOLUTION

Here's another approach, using a single legend, but using a fold transform to unpivot, and a calculate transform to assign a unique 'grouping' value that can be coloured accordingly. This solution uses a single mark also:

dmp_1-1650324486236.png

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {
    "values": [
      {"A": 10, "B": 20, "Z": "poop", "created": "2022/01/01"},
      {"A": 10, "B": 10, "Z": "moop", "created": "2022/01/01"},
      {"A": 10, "B": 5, "Z": "doop", "created": "2022/01/01"},
      {"A": 13, "B": 12, "Z": "poop", "created": "2022/02/01"},
      {"A": 33, "B": 10, "Z": "moop", "created": "2022/02/01"},
      {"A": 0, "B": 5, "Z": "doop", "created": "2022/02/01"},
      {"A": 13, "B": 5, "Z": "poop", "created": "2022/03/01"},
      {"A": 3, "B": 2, "Z": "moop", "created": "2022/03/01"},
      {"A": 20, "B": 7, "Z": "doop", "created": "2022/03/01"}
    ]
  },
  "transform": [
    {"timeUnit": "month", "field": "created", "as": "month"},
    {"fold": ["A", "B"], "as": ["series", "value"]},
    {"calculate": "datum.series + ': ' + datum.Z", "as": "grouping"}
  ],
  "mark": "bar",
  "encoding": {
    "x": {"timeUnit": "month", "field": "created", "type": "ordinal"},
    "y": {"field": "value", "type": "quantitative"},
    "color": {"field": "grouping"}
  }
}

Editor link

Regards,

Daniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




View solution in original post

3 REPLIES 3
dm-p
Super User
Super User

EDIT: I think that this solution doesn't stack properly and series are overlaid. I'm having a think about that one.

Hi @jonespossibly,

It's not entirely clear what you're asking for - perhaps a sketch or mockup may help for future questions.

I've taken a guess though, and hopefully what I've attempted might still get you there if it's not quite what you were envisioning.

If you want to use the inbuilt legend functionality, you're best splitting into layers and either using two different encoding channels for the colour, (e.g. color and fill), or specifying an independent scale resolution for each color channel so that the legend treats them separately (I've gone for this option in my spec below).

You will also need to specify a range for each color encoding's scale, as they will resolve the same color values if treated as nominal (due to them being the same across series).

I've also specified a manual title for each legend so that it's apparent as to what's what, as Vega-Lite will just title it with the field you're using if not specified:

dmp_0-1650322558002.png

 

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {
    "values": [
      {"A": 10, "B": 20, "Z": "poop", "created": "2022/01/01"},
      {"A": 10, "B": 10, "Z": "moop", "created": "2022/01/01"},
      {"A": 10, "B": 5, "Z": "doop", "created": "2022/01/01"},
      {"A": 13, "B": 12, "Z": "poop", "created": "2022/02/01"},
      {"A": 33, "B": 10, "Z": "moop", "created": "2022/02/01"},
      {"A": 0, "B": 5, "Z": "doop", "created": "2022/02/01"},
      {"A": 13, "B": 5, "Z": "poop", "created": "2022/03/01"},
      {"A": 3, "B": 2, "Z": "moop", "created": "2022/03/01"},
      {"A": 20, "B": 7, "Z": "doop", "created": "2022/03/01"}
    ]
  },
  "resolve": {"scale": {"color": "independent"}},
  "layer": [
    {
      "mark": "bar",
      "encoding": {
        "x": {"timeUnit": "month", "field": "created", "type": "ordinal"},
        "y": {"field": "A", "type": "quantitative"},
        "color": {"field": "Z", "legend": {"title": "Z (A Series)"}}
      }
    },
    {
      "mark": "bar",
      "encoding": {
        "x": {"timeUnit": "month", "field": "created", "type": "ordinal"},
        "y": {"field": "B", "type": "quantitative"},
        "color": {
          "field": "Z",
          "legend": {"title": "Z (B Series)"},
          "scale": {"range": ["red", "green", "blue"]}
        }
      }
    }
  ]
}

 

Regards,

Daniel

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




Here's another approach, using a single legend, but using a fold transform to unpivot, and a calculate transform to assign a unique 'grouping' value that can be coloured accordingly. This solution uses a single mark also:

dmp_1-1650324486236.png

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {
    "values": [
      {"A": 10, "B": 20, "Z": "poop", "created": "2022/01/01"},
      {"A": 10, "B": 10, "Z": "moop", "created": "2022/01/01"},
      {"A": 10, "B": 5, "Z": "doop", "created": "2022/01/01"},
      {"A": 13, "B": 12, "Z": "poop", "created": "2022/02/01"},
      {"A": 33, "B": 10, "Z": "moop", "created": "2022/02/01"},
      {"A": 0, "B": 5, "Z": "doop", "created": "2022/02/01"},
      {"A": 13, "B": 5, "Z": "poop", "created": "2022/03/01"},
      {"A": 3, "B": 2, "Z": "moop", "created": "2022/03/01"},
      {"A": 20, "B": 7, "Z": "doop", "created": "2022/03/01"}
    ]
  },
  "transform": [
    {"timeUnit": "month", "field": "created", "as": "month"},
    {"fold": ["A", "B"], "as": ["series", "value"]},
    {"calculate": "datum.series + ': ' + datum.Z", "as": "grouping"}
  ],
  "mark": "bar",
  "encoding": {
    "x": {"timeUnit": "month", "field": "created", "type": "ordinal"},
    "y": {"field": "value", "type": "quantitative"},
    "color": {"field": "grouping"}
  }
}

Editor link

Regards,

Daniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


My course: Introduction to Developing Power BI Visuals


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




a haaa!  Excellent.  I think this one will work.  I'll give it a go shortly with the real data, but the poops, moops and doops look right in this.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors