Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I need a snazzy chart that can show a stacked bar chart with a bullet. I would like to use Deneb, but I need some examples. All of the examples I have researched have the "colors" in the bar chart as categories, but I need to use measures.
The stacks are phases of spend: [Paid],[Committed],[Pipeline],[Planned]. All measures.
The bullet is the reforecast.
It's possible that any of my measures could be negative.
I would prefer a traditional bullet looking chart, but I've built a janky "put one chart over another" in Desktop with standard visuals. It's fine - unless someone needs to scroll and then it doesn't work.
Does anyone have any examples they could share with me? Or wise words of wisdom? Many, many thanks!
https://kauffman.box.com/s/ukyd23m2utb26pc5a7cnmyc4jxfjk3pn
@deneb, @stackedbar
Solved! Go to Solution.
@dkernen2, the updated .pbix with this chart in Deneb can be downloaded here.
If this is enough to get you going please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.
Hey @dkernen2. Because your concatenated charts are all the same with only the underlying x-axis encoding changing, as a first step I would suggest switching from a concatenated layout composition to a faceted layout composition. This will allow you to specify encodings once. This will require a fold transform to unpivot the $ columns. You can then facet on the "key" column that results from the fold.
I have created a spec (version with coments can be found in the code block below) that will hopefully be helpful and can act as a guide. I added checkboxes to act like slicers to show how the bar heights update dynamically. For the labels, I implemented a strategy to include them either inside its bar or outside its bar depending upon how large the bar is. Because creating the ability to only show labels only if they fit requires a lot of workarounds in vega-lite, I instead utilized the limit property to fit as much of the label text as possible before truncation. Hope this suffices.
Here is documentation on setting up drillthrough with Deneb
Here is documentation on formatting values in Deneb
I hope this helps.
{
"$schema": "https://vega.github.io/schema/vega-lite/v5.json",
"transform": [
{
//ignore - used to simulate slicer selections
"filter": "indexof(selectedDepartments, datum['Department']) >=0"
},
{
//unpivots the specified columns and return key/value pairs
"fold": ["Budgeted $", "Paid $"]
},
{
//get min and max values grouped by key
"joinaggregate": [
{"op": "min", "field": "value", "as": "min"},
{"op": "max", "field": "value", "as": "max"}
],
"groupby": ["key"]
},
{
//use the min/max values to determine if the current bar is > or < 50% of the faceted width
//if < 50% align right, otherwise align left
"calculate": "(datum['max']-datum['value'])/(datum['max']-datum['min']) < 0.5 ? 'right' : 'left'",
"as": "align"
},
{
"calculate": "1",
"as": "labelOpacity"
},
{
//ignore - used to add text to the labels for demonstration purposes only
"calculate": "datum['label'] + (datum['align']==='left'? ' that is OUTSIDE its bar' : ' that is INSIDE its bar')",
"as": "label"
}
],
"facet": {
//each key is given its own column
"column": {
"field": "key",
"sort": "ascending",
"title": null,
"header": {"labelFontSize": 14}
}
},
"spec": {
//width and height of each facet
"width": 400,
"height": 200,
"view": {"stroke": "transparent"},
"encoding": {
"x": {"type": "quantitative", "field": "value"},
"y": {
"type": "nominal",
"field": "Department",
"sort": {"field": "id"},
"axis": {"labelFont": "Helvetica", "labelFontSize": 14}
}
},
"layer": [
{"mark": {"type": "bar", "color": "green"}}, //bar color just entered once; applied to all facets
{
"mark": {
"type": "text",
"limit": {"expr": "datum['align'] === 'right' ? (scale('x', datum['value'])-10) : child_width-(scale('x', datum['value'])-10)"},
"text": {"expr": "datum['label']"},
"align": {"expr": "datum['align']"},
"xOffset": {"expr": "datum['align'] === 'left' ? 5 : -5"}, //spacing for each label from bar
"fill": {
"expr": "luminance('green') < 0.5 && datum['align'] === 'right' ? 'white' : 'black'" //if the label is to be pklaced inside, calculate the luminance of the bar to determine if the label fill should be white or black
}
}
}
]
},
"config": {
"axis": {"grid": false, "ticks": false, "domain": false, "title": null},
"axisQuantitative": {"labels": false}
},
"data": {
"name": "dataset",
"values": [
{
"id": 1,
"Department": "A",
"label": "This is a long label",
"Budgeted $": 15,
"Paid $": 17
},
{
"id": 2,
"Department": "B",
"label": "This is a long label",
"Budgeted $": 5,
"Paid $": 14
},
{
"id": 3,
"Department": "C",
"label": "This is a long label",
"Budgeted $": 6,
"Paid $": 2
},
{
"id": 4,
"Department": "D",
"label": "This is a long label",
"Budgeted $": 4,
"Paid $": 7
},
{
"id": 5,
"Department": "E",
"label": "This is a long label",
"Budgeted $": 3,
"Paid $": 15
},
{
"id": 6,
"Department": "F",
"label": "This is a long label",
"Budgeted $": 6,
"Paid $": 8
},
{
"id": 7,
"Department": "G",
"label": "This is a long label",
"Budgeted $": 7,
"Paid $": 7
}
]
},
//ignore - params set up to simulate external slicer selections
"params": [
{
"name": "DepartmentA",
"value": true,
"bind": {"input": "checkbox", "name": "A"}
},
{
"name": "DepartmentB",
"value": true,
"bind": {"input": "checkbox", "name": "B"}
},
{
"name": "DepartmentC",
"value": true,
"bind": {"input": "checkbox", "name": "C"}
},
{
"name": "DepartmentD",
"value": true,
"bind": {"input": "checkbox", "name": "D"}
},
{
"name": "DepartmentE",
"value": true,
"bind": {"input": "checkbox", "name": "E"}
},
{
"name": "DepartmentF",
"value": true,
"bind": {"input": "checkbox", "name": "F"}
},
{
"name": "DepartmentG",
"value": true,
"bind": {"input": "checkbox", "name": "G"}
},
{
"name": "selectedDepartments",
"expr": "[DepartmentA?'A':'',DepartmentB?'B':'',DepartmentC?'C':'',DepartmentD?'D':'',DepartmentE?'E':'',DepartmentF?'F':'',DepartmentG?'G':'']"
},
{"name": "test", "expr": "[DepartmentE]"}
]
}
I am still working on enhancing this visual, but I am having a few problems. Could you offer some guidance?
Where can I change the font and font size of the Y axis?
How can I only show only data labels when they fit reasonably?
How can I show the proper formats in the data labels and the tooltips?
Can I enable drill-through?
https://kauffman.box.com/s/3ypt201mq20hmycva1g3jn28ugw1bre7
Hey @dkernen2. Because your concatenated charts are all the same with only the underlying x-axis encoding changing, as a first step I would suggest switching from a concatenated layout composition to a faceted layout composition. This will allow you to specify encodings once. This will require a fold transform to unpivot the $ columns. You can then facet on the "key" column that results from the fold.
I have created a spec (version with coments can be found in the code block below) that will hopefully be helpful and can act as a guide. I added checkboxes to act like slicers to show how the bar heights update dynamically. For the labels, I implemented a strategy to include them either inside its bar or outside its bar depending upon how large the bar is. Because creating the ability to only show labels only if they fit requires a lot of workarounds in vega-lite, I instead utilized the limit property to fit as much of the label text as possible before truncation. Hope this suffices.
Here is documentation on setting up drillthrough with Deneb
Here is documentation on formatting values in Deneb
I hope this helps.
{
"$schema": "https://vega.github.io/schema/vega-lite/v5.json",
"transform": [
{
//ignore - used to simulate slicer selections
"filter": "indexof(selectedDepartments, datum['Department']) >=0"
},
{
//unpivots the specified columns and return key/value pairs
"fold": ["Budgeted $", "Paid $"]
},
{
//get min and max values grouped by key
"joinaggregate": [
{"op": "min", "field": "value", "as": "min"},
{"op": "max", "field": "value", "as": "max"}
],
"groupby": ["key"]
},
{
//use the min/max values to determine if the current bar is > or < 50% of the faceted width
//if < 50% align right, otherwise align left
"calculate": "(datum['max']-datum['value'])/(datum['max']-datum['min']) < 0.5 ? 'right' : 'left'",
"as": "align"
},
{
"calculate": "1",
"as": "labelOpacity"
},
{
//ignore - used to add text to the labels for demonstration purposes only
"calculate": "datum['label'] + (datum['align']==='left'? ' that is OUTSIDE its bar' : ' that is INSIDE its bar')",
"as": "label"
}
],
"facet": {
//each key is given its own column
"column": {
"field": "key",
"sort": "ascending",
"title": null,
"header": {"labelFontSize": 14}
}
},
"spec": {
//width and height of each facet
"width": 400,
"height": 200,
"view": {"stroke": "transparent"},
"encoding": {
"x": {"type": "quantitative", "field": "value"},
"y": {
"type": "nominal",
"field": "Department",
"sort": {"field": "id"},
"axis": {"labelFont": "Helvetica", "labelFontSize": 14}
}
},
"layer": [
{"mark": {"type": "bar", "color": "green"}}, //bar color just entered once; applied to all facets
{
"mark": {
"type": "text",
"limit": {"expr": "datum['align'] === 'right' ? (scale('x', datum['value'])-10) : child_width-(scale('x', datum['value'])-10)"},
"text": {"expr": "datum['label']"},
"align": {"expr": "datum['align']"},
"xOffset": {"expr": "datum['align'] === 'left' ? 5 : -5"}, //spacing for each label from bar
"fill": {
"expr": "luminance('green') < 0.5 && datum['align'] === 'right' ? 'white' : 'black'" //if the label is to be pklaced inside, calculate the luminance of the bar to determine if the label fill should be white or black
}
}
}
]
},
"config": {
"axis": {"grid": false, "ticks": false, "domain": false, "title": null},
"axisQuantitative": {"labels": false}
},
"data": {
"name": "dataset",
"values": [
{
"id": 1,
"Department": "A",
"label": "This is a long label",
"Budgeted $": 15,
"Paid $": 17
},
{
"id": 2,
"Department": "B",
"label": "This is a long label",
"Budgeted $": 5,
"Paid $": 14
},
{
"id": 3,
"Department": "C",
"label": "This is a long label",
"Budgeted $": 6,
"Paid $": 2
},
{
"id": 4,
"Department": "D",
"label": "This is a long label",
"Budgeted $": 4,
"Paid $": 7
},
{
"id": 5,
"Department": "E",
"label": "This is a long label",
"Budgeted $": 3,
"Paid $": 15
},
{
"id": 6,
"Department": "F",
"label": "This is a long label",
"Budgeted $": 6,
"Paid $": 8
},
{
"id": 7,
"Department": "G",
"label": "This is a long label",
"Budgeted $": 7,
"Paid $": 7
}
]
},
//ignore - params set up to simulate external slicer selections
"params": [
{
"name": "DepartmentA",
"value": true,
"bind": {"input": "checkbox", "name": "A"}
},
{
"name": "DepartmentB",
"value": true,
"bind": {"input": "checkbox", "name": "B"}
},
{
"name": "DepartmentC",
"value": true,
"bind": {"input": "checkbox", "name": "C"}
},
{
"name": "DepartmentD",
"value": true,
"bind": {"input": "checkbox", "name": "D"}
},
{
"name": "DepartmentE",
"value": true,
"bind": {"input": "checkbox", "name": "E"}
},
{
"name": "DepartmentF",
"value": true,
"bind": {"input": "checkbox", "name": "F"}
},
{
"name": "DepartmentG",
"value": true,
"bind": {"input": "checkbox", "name": "G"}
},
{
"name": "selectedDepartments",
"expr": "[DepartmentA?'A':'',DepartmentB?'B':'',DepartmentC?'C':'',DepartmentD?'D':'',DepartmentE?'E':'',DepartmentF?'F':'',DepartmentG?'G':'']"
},
{"name": "test", "expr": "[DepartmentE]"}
]
}
@dkernen2, the updated .pbix with this chart in Deneb can be downloaded here.
If this is enough to get you going please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.
Thank you so much!!!
Hey @dkernen2, I used your .pbix file (thanks for providing that), and your notes to come up with the following. To see the Vega-Lite spec for this, click here. Before I get this into your Power BI file via Deneb, I wanted to make sure that I was on the right track. If you want a different look, can you describe it and/or provide an image?
@giammariam Kapow! Unbelievable! I am speechless!
Visually, the only thing that isn't perfect is the height of the "bullet." It could be smaller to not detract from the stacks, which should stand out more. I put the "old Tableau" image and my janky "two-charts-on-top-of-each-other" in the same PBIX referenced below.
With deepest gratitude!
Hey @dkernen2. Thanks for the feedback. Let me know if this is better. Once you think everything looks ok, I'll get it into Deneb.
The updated vega-lite gist can be found here.
(note - you can't see my cursor in the screenshot but I was hovering over the Planned block for department Black)
@giammariam Gorgeous! The only thing is that I need the measures to be in a certain order (at least when the values are positive). I think it's currently sorting by measure name. Thank you so so much!
@dkernen2, ah yes, I had that but somehow the that update got lost at some point. Better?
The updated vega-lite gist can be found here.
Thanks @Erokor - we have some restrictions on using custom visuals, which is why I was leaning toward Deneb. Also, the custom visual you suggested only allows for three stacks, and I need four.
If you have some budget laying around, I know Bullet Charts by XViz are great for this. I've personally used it and paid the premium as I couldn't get the interactivity I wanted out of a Deneb visual.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |