Deneb/Vega-Lite can be used to generate cycle plots that expose period trends, which can be especially useful for datasets with seasonality. Further, these cycle plots can be augmented with additional analyses. The examples herein showcase monthly data with mean, variance, and linear regression for 3 different datasets:
- Arctic Sea Ice Extents
- Antarctic Sea Ice Extents
- Iqaluit, Nunavut, Canada Average Air Temperature
As well, a screen widget is used to control the display of certain elements and thus give the user the ability to unclutter the visual as desired.
In each of the 3 datasets, only 2 columns of data ([Date], [Value]) are provided to the Deneb visual; all other required attributes are calculated within the Deneb/Vega-Lite code.
These examples illustrate a number of Deneb/Vega-Lite features, including:
0 - General:
- a “title” block with subtitle
- a shared “transform” block to extend the dataset with:
- 4x “calculate” transforms to determine the year, month number, month name, and day of month
- 2x “joinaggregate” transforms to determine the maximum year and mean
- a shared “params” block to enhance the visual with:
- 3x parameters for the line, mean, and regression colours
- a parameter for the desired variance direction (up; down)
- 2x parameters for the variance colours (+ve; -ve) that use the desired variance direction parameter
- 2x parameters for the Y offset of the variance and regession metric text boxes
- a screen widget to select the desired augmentation option (variance, regression, both, none)
- a “facet” block to create small multiples by month
- facet labels and block title oriented to the bottom
- a shared “encoding” block for the Y axis with:
- a format setting for the labels to use SI units
- a “scale” array to set the range (minimum, maximum)
- a “layer” block for the line, mean, variance metrics, and regression metrics
1 - Data:
- a “line” mark using the line colour parameter value
2 - Mean:
- a “rule” mark using the mean colour parameter value and only a Y coordinate
3 - Variance:
- a nested “transform” block with:
- 2x “filter” transforms to use the augmentation option screen widget to only show data when [Variance] or [Both] is selected and to restrict the facetted dataset to a single record
- a “calculate” transform to determine the variance
- a nested “layer” block for the arrow body, arrow head, and metrics:
Arrow Body:
- a “rule” mark using the +ve and -ve variance colour parameters
Arrow Head:
- a “point” mark using conditional shape (+ve variance = triangle-up, -ve variance = triangle-down) and conditional colour (matches the “arrow body” above)
Variance Metrics:
- a “text” mark for the hard-coded title using the Y offset parameter
- a “text” mark for the hard-coded array of row headers using the Y offset parameter
- a “text” mark for the formatted row values using the Y offset parameter
4 - Regression:
- a nested “transform” block with:
- a “filter” transform to use the augmentation option screen widget to only show data when [Regression] or [Both] is selected
- a nested “layer” block for the regression line and regression metrics:
Regression Line:
- a nested “regression” transform using the “linear” method
- a “rule” mark using the regression colour parameter
Variance Metrics:
- a “text” mark for the hard-coded title using the Y offset parameter
- a “text” mark for the hard-coded array of row headers using the Y offset parameter
Metric values:
- a nested "transform block with:
- a 2nd iteration of the linear “regression”, this time exposing the regression parameters
- 2x “calculate” transforms to use the exposed parameters to surface the regression [Slope] and [Rsquared] values
- a “text” mark for the formatted row values using the Y offset parameter
Deneb/Vega-Lite JSON Code:
{
"title": {
"anchor": "start",
"align": "left",
"offset": 20,
// modify main title text as desired
"text": "Power BI Augmented Cycle Plot using Deneb - Antarctic Sea Ice Extent",
"font": "Verdana",
"fontSize": 24,
"fontWeight": "bold",
"fontStyle": "normal",
// modify subtitle text as desired
"subtitle": "(Data Source = NOAA/NSIDC Sea Ice Index https://noaadata.apps.nsidc.org/NOAA/G02135/seaice_analysis/)",
"subtitleColor": "#969696",
"subtitleFont": "Verdana",
"subtitleFontSize": 16,
"subtitleFontWeight": "normal",
"subtitleFontStyle": "italic"
},
"data": {
"name": "dataset"
},
"transform": [
{
"calculate": "year( datum['Date'] )",
"as": "_year_number"
},
{
"joinaggregate": [
{
"op": "max",
"field": "_year_number",
"as": "_max_year_number"
}
]
},
{
"calculate": "month( datum['Date'] )",
"as": "_month_number"
},
{
"calculate": "monthFormat( datum['_month_number'] )",
"as": "_month_name"
},
{
"calculate": "date( datum['Date'] )",
"as": "_day_of_month"
},
{
"joinaggregate": [
{
"op": "mean",
"field": "Value",
"as": "_mean_value"
}
],
"groupby": [
"_month_number"
]
}
],
"params": [
{
"name": "_data_line_colour",
"value": "#3F9AE9"
},
{
"name": "_mean_line_colour",
"value": "#969696"
},
{
"name": "_regression_colour",
"value": "#1A1A1A"
},
{
// set desired variance direction (up; down)
"name": "_desired_variance_direction",
"value": "up"
},
{
"name": "_variance_negative_colour",
"expr": "if( _desired_variance_direction == 'up', '#D8432E', '#009755' )"
},
{
"name": "_variance_positive",
"expr": "if( _desired_variance_direction == 'down', '#D8432E', '#009755' )"
},
{
// set as desired
"name": "_variance_metric_y",
"value": 2
},
{
// set as desired
"name": "_regression_metric_y",
"value": 548
},
{
"name": "_option",
"value": "Variance",
"bind": {
"input": "radio",
"options": [
"None",
"Variance",
"Regression",
"Both"
],
"name": "Option: "
}
}
],
"spacing": 0,
"facet": {
"column": {
"field": "_month_name",
"sort": {
"op": "max",
"field": "_month_number"
},
"header": {
"titleOrient": "bottom",
"titleAnchor": "start",
"titleAlign": "left",
"titlePadding": 10,
"titleFont": "Segoe UI",
"titleFontSize": 18,
"titleFontWeight": "normal",
"titleFontStyle": "italic",
"labelOrient": "bottom",
"labelPadding": 4,
"labelFont": "Segoe UI",
"labelFontSize": 14
},
// modify facet title text as desired
"title": "(negative variance is undesirable)"
}
},
"spec": {
"width": 118,
"height": 620,
"encoding": {
"y": {
"type": "quantitative",
"axis": {
"labelExpr": "format( datum.value, '~s' )",
"labelFont": "Segoe UI",
"labelFontSize": 12,
"tickCount": 9,
// modify title text as desired
"title": "Area (km²)",
"titleFont": "Segoe UI",
"titleFontSize": 16
},
"scale": {
// modify domain to suit the data
"domain": [
0,
24000000
]
}
}
},
"layer": [
{
"name": "FULL_DATA",
"mark": {
"type": "line",
"color": {
"expr": "_data_line_colour"
}
},
"encoding": {
"x": {
"field": "_year_number",
"type": "quantitative",
"axis": null
},
"y": {
"field": "Value"
}
}
},
{
"name": "MEAN",
"mark": {
"type": "rule",
"strokeWidth": 2,
"color": {
"expr": "_mean_line_colour"
}
},
"encoding": {
"y": {
"field": "_mean_value"
}
}
},
{
"name": "VARIANCE",
"transform": [
{
"filter": "_option == 'Variance' || _option == 'Both' ? datum['Date'] > datetime( 1900, 0, 1 ) : datum['Date'] == datetime( 1900, 0, 1 )"
},
{
"filter": "datum['_year_number'] == datum['_max_year_number']"
},
{
"calculate": "datum['Value'] - datum['_mean_value']",
"as": "_variance"
}
],
"layer": [
{
"name": "VARIANCE_ARROW_BODY",
"mark": {
"type": "rule",
"strokeWidth": 4,
"color": {
"expr": "datum['_variance'] < 0 ? _variance_negative_colour : _variance_positive"
}
},
"encoding": {
"x": {
"field": "_max_year_number",
"type": "quantitative"
},
"y": {
"field": "_mean_value"
},
"y2": {
"field": "Value"
}
}
},
{
"name": "VARIANCE_ARROW_HEAD",
"mark": {
"type": "point",
"shape": {
"expr": "datum['_variance'] < 0 ? 'triangle-down' : 'triangle-up'"
},
"size": 10,
"opacity": 1,
"strokeWidth": 4,
"color": {
"expr": "datum['_variance'] < 0 ? _variance_negative_colour : _variance_positive"
}
},
"encoding": {
"x": {
"field": "_max_year_number",
"type": "quantitative"
},
"y": {
"field": "Value"
}
}
},
{
"name": "VARIANCE_METRIC_TITLE",
"transform": [
{
"calculate": "['Variance:']",
"as": "_title"
}
],
"mark": {
"type": "text",
"baseline": "top",
"align": "left",
"yOffset": 2,
"fontSize": 16,
"fontWeight": "bold",
"color": "black",
"x": 2,
"y": {
"expr": "_variance_metric_y + 0"
}
},
"encoding": {
"text": {
"field": "_title",
"type": "nominal"
}
}
},
{
"name": "VARIANCE_METRIC_ROW_HEADER",
"transform": [
{
"calculate": "['Last:', 'Mean:', 'Variance:']",
"as": "_metric_name"
}
],
"mark": {
"type": "text",
"baseline": "top",
"align": "left",
"fontSize": 12,
"color": "black",
"x": 2,
"y": {
"expr": "_variance_metric_y + 24"
}
},
"encoding": {
"text": {
"field": "_metric_name",
"type": "nominal"
}
}
},
{
"name": "VARIANCE_METRIC_VALUE",
"transform": [
{
// modify format strings as desired
"calculate": "[format( datum['Value'], '+.4s' ), format( datum['_mean_value'], '+.4s' ), format( datum['_variance'], '+.4s' )]",
"as": "_metric_value"
}
],
"mark": {
"type": "text",
"baseline": "top",
"align": "right",
"xOffset": 90,
"fontSize": 12,
"color": "black",
"x": 20,
"y": {
"expr": "_variance_metric_y + 24"
}
},
"encoding": {
"text": {
"field": "_metric_value",
"type": "nominal"
}
}
}
]
},
{
"name": "REGRESSION",
"transform": [
{
"filter": "_option == 'Regression' || _option == 'Both' ? datum['Date'] > datetime(1900, 0, 1) : datum['Date'] == datetime(1900, 0, 1)"
}
],
"layer": [
{
"name": "REGRESSION_LINE",
"transform": [
{
"regression": "Value",
"on": "_year_number",
"method": "linear"
}
],
"mark": {
"type": "line",
"strokeWidth": 2,
"color": {
"expr": "_regression_colour"
}
},
"encoding": {
"x": {
"field": "_year_number",
"type": "quantitative"
},
"y": {
"field": "Value",
"type": "quantitative"
}
}
},
{
"name": "REGRESSION_METRIC_TITLE",
"transform": [
{
"calculate": "['Regression:']",
"as": "_title"
},
{
"filter": "datum['_year_number'] == datum['_max_year_number']"
}
],
"mark": {
"type": "text",
"baseline": "top",
"align": "left",
"yOffset": 12,
"fontSize": 16,
"fontWeight": "bold",
"color": "black",
"x": 2,
"y": {
"expr": "_regression_metric_y + 0"
}
},
"encoding": {
"text": {
"field": "_title",
"type": "nominal"
}
}
},
{
"name": "REGRESSION_METRIC_ROW_HEADER",
"transform": [
{
"calculate": "['Method:', 'Slope:', 'R²:']",
"as": "_metric_name"
},
{
"filter": "datum['_year_number'] == datum['_max_year_number']"
}
],
"mark": {
"type": "text",
"baseline": "top",
"align": "left",
"yOffset": 20,
"fontSize": 12,
"color": "black",
"x": 2,
"y": {
"expr": "_regression_metric_y + 12"
}
},
"encoding": {
"text": {
"field": "_metric_name",
"type": "nominal"
}
}
},
{
"name": "REGRESSION_METRIC_VALUE",
"transform": [
{
"regression": "Value",
"on": "_year_number",
"method": "linear",
"params": true
},
{
"calculate": "format( datum['rSquared'], '.4f' )",
"as": "_r2"
},
{
"calculate": "datum['coef'][1]",
"as": "_slope"
},
{
// modify format strings as desired
"calculate": "['Linear', format( datum['_slope'], '+.4s' ), format( datum['_r2'], '+.4f' )]",
"as": "_metric_value"
}
],
"mark": {
"type": "text",
"baseline": "top",
"align": "right",
"yOffset": 20,
"xOffset": 90,
"fontSize": 12,
"color": "black",
"x": 20,
"y": {
"expr": "_regression_metric_y + 12"
}
},
"encoding": {
"text": {
"field": "_metric_value",
"type": "nominal"
}
}
}
]
}
]
}
}
The intent of these examples were not to provide finished visuals, but rather to explore the use of the Deneb custom visual and the Vega-Lite language within Power BI and to serve as starting points for further development.
NOTE: the JSON code can also be used as a template with the adjustment of just seven lines (title text, subtitle text, desired variance direction, variance metric y offset, regression metric y coordinate, facet title, and y axis scale domain/range [min, max]), each of which has been denoted with a Deneb 1.7.x comment.
Also included is the development sample PBIX using three data sources:
1 - Arctic Sea Ice Extent Data: National Oceanic and Atmospheric Administration (NOAA) program at the National Snow and Ice Data Center (NSIDC) https://noaadata.apps.nsidc.org/NOAA/G02135/north/monthly/data/; Sea_Ice_Index_Monthly_Data_by_Year_G02135_v3.0.xlsx, [NH-Extent] sheet (538 rows)
2 - Antarctic Sea Ice Extent Data: same as URL and Excel file as above, but using the [SH-Extent] sheet (538 rows)
3 - Iqaluit, Canada Average Air Temperature: Environment and Climate Change Canada, Iqaluit A Weather Station, 1946-2007, https://climate-change.canada.ca/climate-data/#/monthly-climate-summaries (739 rows)
These examples are provided as-is for information purposes only, and their use is solely at the discretion of the end user; no responsibility is assumed by the author.
Greg
Deneb Example - Cycle Plot - V8.pbix (2.4 MB)