Introduction
In the world of data-driven decision-making, clarity and effectiveness are paramount. The International Business Communication Standards (IBCS) have emerged as a guiding light, illuminating the path toward more actionable and insightful reporting. IBCS sets a gold standard for data visualization and reporting practices, making it easier for businesses to derive actionable insights from their data.
With the inspiration drawn from IBCS reporting principles, we embark on a journey to enhance data understanding and decision-making. Today, we delve into the power of Year-over-Year (YoY) comparisons, utilizing the Deneb Custom Visual, to provide a clear and comprehensive view of how our performance this year stacks up against the previous year. These comparative insights, encompassing both absolute and relative changes, are instrumental in driving informed actions and achieving data-driven success. So, let’s dive in and discover the transformative potential of our approach!
Now, let’s dive into the code and see how these principles translate into effective data visualization.
Credits to Greg Philps and Enterprise DNA (YouTube) where I got the initial idea and code from. I simply adjusted certain parts to my liking:
Look for the measures and replace them with your data:
– Total Costs AC
– Total Costs Ref
– YearMonth
The absolute and relative change are calucated automatically!
The Code
{
"title": {
"anchor": "middle",
"align": "center",
"offset": 10,
"text": "Total Costs Current Year vs Last Year",
"font": "Verdana",
"fontSize": 20,
"fontWeight": "bold",
"fontStyle": "normal",
"subtitle": "by Year/Month",
"subtitleFontSize": 12,
"subtitleFontStyle": "italic"
},
"data": {"name": "dataset"},
"transform": [
{
"calculate": "datum['Total Costs AC'] - datum['Total Costs Ref']",
"as": "_variance_amount"
},
{
"calculate": "datum['_variance_amount'] / datum['Total Costs Ref']",
"as": "_variance_percent"
},
{
"calculate": "datum['_variance_amount'] > 100000 ? 100000 : datum['_variance_amount'] < -100000 ? -100000 : datum['_variance_amount']",
"as": "_threshold_variance_amount"
},
{
"calculate": "datum['_variance_percent'] > 1 ? 1 : datum['_variance_percent']",
"as": "_threshold_variance_percent"
}
],
"params": [
{
"name": "_positive_colour",
"value": "green"
},
{
"name": "_negative_colour",
"value": "red"
},
{
"name": "_end_symbol_colour",
"value": "black"
},
{
"name": "_py_colour",
"value": "lightgrey"
},
{
"name": "_cy_colour",
"value": "darkgrey"
}
],
"vconcat": [
{
"name": "LEGEND",
"width": 800,
"height": 1,
"data": {
"values": [
{
"legend_id": 1,
"legend_size": 1,
"legend_label": "Previous Year"
},
{
"legend_id": 2,
"legend_size": 1,
"legend_label": "Current Year"
}
]
},
"mark": {
"type": "arc",
"radius": 0
},
"encoding": {
"theta": {
"field": "legend_size",
"type": "quantitative"
},
"color": {
"field": "legend_label",
"type": "nominal",
"scale": {
"domain": [
"Current Year",
"Previous Year"
],
"range": [
{"expr": "_cy_colour"},
{"expr": "_py_colour"}
]
},
"legend": {
"orient": "top-left",
"direction": "horizontal",
"title": null,
"offset": 0,
"labelColor": "black",
"labelFont": "Segoe UI",
"labelFontSize": 12,
"labelFontStyle": "italic",
"symbolSize": 200,
"symbolType": "circle"
}
}
}
},
{
"name": "VARIANCE_PERCENT",
"width": 980,
"height": 100,
"layer": [
{
"name": "PERCENT_END_SYMBOL",
"mark": {
"type": "point",
"shape": "circle"
},
"encoding": {
"x": {
"field": "YearMonth",
"type": "nominal",
"axis": null
},
"y": {
"field": "_threshold_variance_percent",
"type": "quantitative",
"scale": {
"nice": true
}
},
"color": {
"value": {
"expr": "_end_symbol_colour"
}
}
}
},
{
"name": "PERCENT",
"mark": {
"type": "bar",
"tooltip": true,
"width": {"band": 0.05}
},
"encoding": {
"x": {
"field": "YearMonth",
"type": "nominal"
},
"y": {
"field": "_threshold_variance_percent",
"type": "quantitative",
"scale": {
"nice": true
},
"axis": {
"formatType": "pbiFormat",
"format": "#%",
"labels": false,
"domain": false,
"ticks": false,
"gridColor": {
"condition": {
"test": "datum.value == 0",
"value": "black"
},
"value": "transparent"
},
"gridWidth": {
"condition": {
"test": "datum.value == 0",
"value": 2
},
"value": 1
}
},
"title": null
},
"color": {
"condition": [
{
"test": "datum['_variance_percent'] < 0",
"value": {
"expr": "_negative_colour"
}
}
],
"value": {
"expr": "_positive_colour"
}
},
"tooltip": [
{
"field": "YearMonth",
"type": "nominal"
},
{
"field": "Total Costs AC",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,0"
},
{
"field": "Total Costs Ref",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,0"
},
{
"field": "_variance_amount",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,0",
"title": "Variance"
},
{
"field": "_variance_percent",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#%",
"title": "Variance %"
}
]
}
},
{
"name": "PERCENT_LABEL",
"mark": {
"type": "text",
"color": "black",
"align": "center",
"yOffset": {
"expr": "datum['_variance_percent'] < 0 ? 7 : -5"
},
"baseline": {
"expr": "datum['_variance_percent'] < 0 ? 'top' : 'bottom'"
}
},
"encoding": {
"text": {
"field": "_variance_percent",
"type": "quantitative",
"format": "+#.0%"
},
"x": {
"field": "YearMonth",
"type": "nominal"
},
"y": {
"field": "_threshold_variance_percent",
"type": "quantitative"
}
}
},
{
"name": "PERCENT_INDICATOR",
"mark": {
"type": "point",
"shape": {
"expr": "datum['_variance_percent'] < 0 ? 'triangle-down' : 'triangle-up'"
},
"size": 150,
"yOffset": {
"expr": "datum['_variance_percent'] < 0 ? 24 : -24"
}
},
"encoding": {
"x": {
"field": "YearMonth",
"type": "nominal"
},
"y": {
"field": "_threshold_variance_percent",
"type": "quantitative"
},
"color": {
"condition": [
{
"test": "datum['_variance_percent'] < -1",
"value": {
"expr": "_negative_colour"
}
},
{
"test": "datum['_variance_percent'] > 1",
"value": {
"expr": "_positive_colour"
}
}
],
"value": "transparent"
}
}
}
]
}
,
{
"name": "VARIANCE_AMOUNT",
"width": 980,
"height": 100,
"layer": [
{
"name": "AMOUNT",
"mark": {
"type": "bar",
"tooltip": true,
"xOffset": 0,
"width": {"band": 0.2}
},
"encoding": {
"x": {
"field": "YearMonth",
"type": "nominal",
"axis": null
},
"y": {
"field": "_threshold_variance_amount",
"type": "quantitative",
"axis": {
"domain": false,
"ticks": false,
"labels":false,
"grid": true,
"gridColor": {
"condition": {
"test": "datum.value == 0",
"value": "black"
},
"value": "transparent"
},
"gridWidth": {
"condition": {
"test": "datum.value == 0",
"value": 2
},
"value": 0
}
},
"title": null
},
"color": {
"condition": [
{
"test": "datum['_threshold_variance_amount'] < 0",
"value": {
"expr": "_negative_colour"
}
}
],
"value": {
"expr": "_positive_colour"
}
},
"tooltip": [
{
"field": "YearMonth",
"type": "nominal"
},
{
"field": "Total Costs AC",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,0"
},
{
"field": "Total Costs Ref",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,0"
},
{
"field": "_variance_amount",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,0",
"title": "Variance"
},
{
"field": "_variance_percent",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#%",
"title": "Variance %"
}
]
}
},
{
"name": "AMOUNT_LABEL",
"mark": {
"type": "text",
"color": "black",
"align": "center",
"yOffset": {
"expr": "datum['_threshold_variance_amount'] < 0 ? 5 : -3"
},
"baseline": {
"expr": "datum['_threshold_variance_amount'] < 0 ? 'top' : 'bottom'"
}
},
"encoding": {
"text": {
"field": "_variance_amount",
"type": "quantitative",
"format": "+,.0f"
},
"x": {
"field": "YearMonth",
"type": "nominal"
},
"y": {
"field": "_threshold_variance_amount",
"type": "quantitative"
}
}
},
{
"name": "AMOUNT_INDICATOR",
"mark": {
"type": "point",
"shape": {
"expr": "datum['_threshold_variance_amount'] < 0 ? 'triangle-down' : 'triangle-up'"
},
"size": 150,
"yOffset": {
"expr": "datum['_threshold_variance_amount'] < 0 ? 24 : -24"
}
},
"encoding": {
"x": {
"field": "YearMonth",
"type": "nominal"
},
"y": {
"field": "_threshold_variance_amount",
"type": "quantitative"
},
"color": {
"condition": [
{
"test": "datum['_variance_amount'] < -200",
"value": {
"expr": "_negative_colour"
}
},
{
"test": "datum['_variance_amount'] > +200",
"value": {
"expr": "_positive_colour"
}
}
],
"value": "transparent"
}
}
}
]
},
{
"name": "BAR_CHART",
"width": 980,
"height": 100,
"encoding": {
"x": {
"field": "YearMonth",
"type": "nominal",
"axis": {
"labelAngle": 0,
"titlePadding": 10,
"title": null
}
},
"y": {
"type": "quantitative",
"title": null,
"axis": {
"domain": false,
"ticks": false,
"labels": false,
"gridColor": {
"condition": {
"test": "datum.value == 0",
"value": "black"
},
"value": "#E3E3E3"
},
"gridWidth": {
"condition": {
"test": "datum.value == 0",
"value": 2
},
"value": 1
}
}
},
"tooltip": [
{
"field": "YearMonth",
"type": "nominal"
},
{
"field": "Total Costs AC",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,0"
},
{
"field": "Total Costs Ref",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,0"
},
{
"field": "_variance_amount",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,0",
"title": "Variance"
},
{
"field": "_variance_percent",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#%",
"title": "Variance %"
}
]
},
"layer": [
{
"name": "PREVIOUS_YEAR",
"mark": {
"type": "bar",
"tooltip": true,
"xOffset": 0,
"width": {"band": 0.5},
"color": {
"expr": "_py_colour"
}
},
"encoding": {
"y": {"field": "Total Costs Ref"}
}
},
{
"name": "CURRENT_YEAR",
"mark": {
"type": "bar",
"tooltip": true,
"xOffset": 20,
"width": {"band": 0.5},
"color": {
"expr": "_cy_colour"
}
},
"encoding": {
"y": {"field": "Total Costs AC"}
}
},
{
"name": "CY_LABEL",
"mark": {
"type": "text",
"color": "black",
"align": "left",
"yOffset": -10
},
"encoding": {
"text": {
"field": "Total Costs AC",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#,0"
},
"x": {
"field": "YearMonth",
"type": "nominal"
},
"y": {
"field": "Total Costs AC",
"type": "quantitative",
"aggregate": "sum"
}
}
}
]
}
],
"spacing": 0,
"config": {
"style": {
"cell": {
"stroke": "transparent"
}
},
"view": {
"stroke": "transparent"
}
}
}