Vega-Lite (Deneb) | Waterfall Chart (Start & End Column Problem)
I have hit a dead-end trying to create both a BEGIN / END column for a waterfall chart.
I've managed to create the END column which correctly sits at the end of the X-axis (Jan to Dec). However, I'm unable to successfully add the BEGIN column to appear before the Jan on the x-axis. It either doesn't appear, or sits after Jan insteaod of before. Sorting the x-axis doesn't appear to work either.
Is this possible to do directly in Vega-Lite?
Here is the link to the pbix: waterfall_pbix_file
Here is a screen shot of my current failed attempts.
I hope I've provided enough information. Thank you for your help in advance!
{
"data": {
"name": "dataset"
},
"transform": [
{
"sort": [
{
"field": "MonthNo"
}
],
"window": [
{
"op": "lag",
"field": "salesCY",
"as": "salesCY_previous"
},
{
"op": "lag",
"field": "Month",
"as": "month_previous"
},
{
"op": "lead",
"field": "salesCY",
"as": "salesCY_following"
},
{
"op": "lead",
"field": "Month",
"as": "month_following"
}
]
},
{
"calculate": "datum.month_previous == null ? 'BEGIN' : datum.month_previous",
"as": "month_previous"
},
{
"calculate": "datum.month_following == null ? 'END' : datum.month_following",
"as": "month_following"
},
{
"calculate": "datum.month_previous === 'BEGIN' ? -999 : datum.month_following === 'END' ? 999 : datum.MonthNo",
"as": "sort_order"
}
],
"width": 500,
"height": 200,
"layer": [
{
"mark": {
"type": "bar",
"fill": {
"expr": "datum.salesCY > datum.salesCY_previous ? '#0291DC' : '#F0535A' "
},
"width": {
"band": 0.8
}
}
},
{
"transform": [
{
"filter": "datum.month_previous == 'BEGIN'"
}
],
"mark": {
"type": "bar",
"fill": "lightgrey",
"stroke": "black",
"strokeWidth": 0.5,
"width": {
"band": 0.8
}
},
"encoding": {
"x": {
"field": "month_previous",
"type": "nominal",
"axis": {
"title": null,
"labels": true,
"labelFontSize": 15,
"labelColor": "grey",
"labelExpr": "datum.label[0]",
"labelAngle": 0,
"labelPadding": 15,
"domain": false,
"ticks": false
},
"sort": {
"field": "sort_order",
"order": "ascending"
},
"scale": {
"zero": true
}
},
"y2": {
"datum": 0,
"type": "quantitative",
"axis": {
"title": null,
"labels": false,
"domain": false,
"ticks": false,
"grid": false
}
},
"y": {
"field": "salesCY",
"type": "quantitative",
"axis": {
"title": null,
"labels": false,
"domain": false,
"ticks": false,
"grid": false
}
}
}
},
{
"transform": [
{
"filter": "datum.month_following == 'END'"
}
],
"mark": {
"type": "bar",
"fill": "black",
"width": {
"band": 0.8
}
},
"encoding": {
"x": {
"field": "month_following",
"type": "nominal",
"axis": {
"title": null,
"labels": true,
"labelColor": "grey",
"labelExpr": "datum.label[0]",
"labelAngle": 0,
"domain": false,
"ticks": false
},
"sort": null,
"scale": {
"zero": true
}
},
"y2": {
"datum": 0,
"type": "quantitative",
"axis": {
"title": null,
"labels": false,
"domain": false,
"ticks": false,
"grid": false
}
},
"y": {
"field": "salesCY",
"type": "quantitative",
"axis": {
"title": null,
"labels": false,
"domain": false,
"ticks": false,
"grid": false
}
}
}
}
],
"encoding": {
"x": {
"field": "Month",
"type": "nominal",
"axis": {
"title": null,
"labels": true,
"labelColor": "grey",
"labelExpr": "datum.label[0]",
"labelAngle": 0,
"domain": false,
"ticks": false
},
"sort": {
"field": "sort_order",
"type": "ordinal"
},
"scale": {
"zero": true
}
},
"y2": {
"field": "salesCY_previous",
"type": "quantitative",
"axis": {
"title": null,
"labels": false,
"domain": false,
"ticks": false,
"grid": false
}
},
"y": {
"field": "salesCY",
"type": "quantitative",
"axis": {
"title": null,
"labels": false,
"domain": false,
"ticks": false,
"grid": false
}
}
}
}
Solution
Try this:
{
"data": {
"name": "dataset"
},
"transform": [
{
"sort": [
{
"field": "MonthNo"
}
],
"window": [
{
"op": "lag",
"field": "salesCY",
"as": "salesCY_previous"
},
{
"op": "lag",
"field": "Month",
"as": "month_previous"
},
{
"op": "lead",
"field": "salesCY",
"as": "salesCY_following"
},
{
"op": "lead",
"field": "Month",
"as": "month_following"
}
]
},
{
"calculate": "datum.month_previous == null ? 'BEGIN' : datum.month_previous",
"as": "month_previous"
},
{
"calculate": "datum.month_following == null ? 'END' : datum.month_following",
"as": "month_following"
},
{
"calculate": "datum.MonthNo",
"as": "sort_order"
}
],
"width": 500,
"height": 200,
"layer": [
{
"mark": {
"type": "bar",
"fill": {
"expr": "datum.salesCY > datum.salesCY_previous ? '#0291DC' : '#F0535A' "
},
"width": {
"band": 0.8
}
}
},
{
// CANT SEEM TO FIX THIS MARK TO APPEAR BEFORE THE JAN MONTH COLUMN
"transform": [
{
"filter": "datum.month_previous == 'BEGIN'"
},
{
"calculate": "-1",
"as": "sort_order"
}
],
"mark": {
"type": "bar",
"fill": "lightgrey",
"stroke": "black",
"strokeWidth": 0.5,
"width": {
"band": 0.8
}
},
"encoding": {
"x": {
"field": "month_previous",
"type": "nominal",
"axis": {
"title": null,
"labels": true,
"labelFontSize": 15,
"labelColor": "grey",
"labelExpr": "datum.label[0]",
"labelAngle": 0,
"labelPadding": 15,
"domain": false,
"ticks": false
},
"sort": null,
"scale": {
"zero": true
}
},
"y2": {
"datum": 0,
"type": "quantitative",
"axis": {
"title": null,
"labels": false,
"domain": false,
"ticks": false,
"grid": false
}
},
"y": {
"field": "salesCY",
"type": "quantitative",
"axis": {
"title": null,
"labels": false,
"domain": false,
"ticks": false,
"grid": false
}
}
}
},
{
"transform": [
{
"filter": "datum.month_following == 'END'"
},
{
"calculate": "1e6",
"as": "sort_order"
}
],
"mark": {
"type": "bar",
"fill": "black",
"width": {
"band": 0.8
}
},
"encoding": {
"x": {
"field": "month_following",
"type": "nominal",
"axis": {
"title": null,
"labels": true,
"labelColor": "grey",
"labelExpr": "datum.label[0]",
"labelAngle": 0,
"domain": false,
"ticks": false
},
"sort": null,
"scale": {
"zero": true
}
},
"y2": {
"datum": 0,
"type": "quantitative",
"axis": {
"title": null,
"labels": false,
"domain": false,
"ticks": false,
"grid": false
}
},
"y": {
"field": "salesCY",
"type": "quantitative",
"axis": {
"title": null,
"labels": false,
"domain": false,
"ticks": false,
"grid": false
}
}
}
}
],
"encoding": {
"x": {
"field": "Month",
"type": "nominal",
"axis": {
"title": null,
"labels": true,
"labelColor": "grey",
"labelExpr": "datum.label[0]",
"labelAngle": 0,
"domain": false,
"ticks": false
},
"sort": {
"field": "sort_order",
"order": "ascending"
},
"scale": {
"zero": true
}
},
"y2": {
"field": "salesCY_previous",
"type": "quantitative",
"axis": {
"title": null,
"labels": false,
"domain": false,
"ticks": false,
"grid": false
}
},
"y": {
"field": "salesCY",
"type": "quantitative",
"axis": {
"title": null,
"labels": false,
"domain": false,
"ticks": false,
"grid": false
}
}
}
}
- Deneb (Vega-lite) - Sorting Axis
- Power Query `List.Generate` runs too slow
- Multi-layer x-axis Date setup in Vega-Lite
- How do I return the value in a column that closest matches another column in Power Query
- Vega-Lite (Deneb) | Waterfall Chart (Start & End Column Problem)
- Power BI Embed URL-multiple filters
- Get Average of Top 3 in a Summarized Table in Power Bi
- How to Merge Duplicate Rows and Sum Numeric Values in Power BI When Cleaning Sales Data from Multiple Regions?
- TOTAL is correct but rows are missing
- Custom Colum based on specific dates in powerquery
- Dax - how to get average value per day where column x value is y?
- Is there a way to create a shareable link for a Power BI report using Power BI REST APIs?
- Fixed values but variable year
- Power bi desktop table visual out of memory on a simple DAX
- Possible to load .SQL file to Power bi?
- Power BI Sorting stacked columns
- Update dataset of the Power BI Report
- Use DAX Query to create table in Power BI
- Click event handler on bar chart in PowerBI embed
- Subtraction of previous period in PowerQuery
- Split column after getting multiple different CSV's into PowerBI/PowerQuery
- Dynamically changing the band based on aggregate chosen from slicer
- Chart (circle) background off not working
- Power Query: Convert existing data set, individual rows into columns based on Dates
- Grouped running total with Power Query M
- PowerBI Azure-Devops: query not showing links (as shown in Msft sample query)
- How to remove visualization of blank sublevel in slicers
- How do I properly use table.group in a PowerQuery query to dynamically summarize different rows and columns?
- How to get all messages post by person in team channels using power apps in teams
- how to change a specific cell in Power Query (M Language)?