Deneb/Vega-Lite can be used to generate an over/under plot (my term for a line chart with dual coloured gradient areas on either side of a reference value). Whereas a standard line chart uses a single colour, the over/under plot uses 2 line colours for segments of the line above and below a reference value. Further, the over and under areas are coloured with gradients. The calculation of all line-reference intersections are done in Deneb/Vega-Lite, with only a simple doubling of the dataset done in Power BI to provide placeholders for each potential “cross” of (or intersection with) the reference line. The example presented herein uses a dataset of Microsoft closing stock prices for the 500 days from June 7, 2023 to October 18, 2024.
Thanks to @DM-P for sharing his knowledge of the Vega-Lite syntax to render inverted gradients.
This example illustrates a number of Deneb/Vega-Lite features, including:
0 - General:
- a “title” block with subtitle
- a “padding” block to adjust the vertical position of the title
- a shared “transform” block to extend the dataset with:
- 2x “joinaggregate” transforms to calculate the record count and reference value (in this case, the mean)
- 3x “window/lead” transforms to calculate the next id, reading, and value of the next record
- 6x “calculate” transforms to determine the cross-type (up, down, above, below), difference (id, reading, value), increment fraction, and intersection reading and value
- a “window/first_value” transform to add the intersection point values to the dataset
- 3x “calculate” transforms to determine the cross type, reading, and value for display for all points (dataset and calculated intersections)
- a “calculate” transform to determine the reading date
- a shared “encoding” block to ensure all marks use the same X and Y axes
- the X axis configured with:
- conditional colour, dash pattern, and width to highlight years and quarter grid and tick lines
- (formatting is based on the [Line Chart with Conditional Axis Properties] example on the Vega-Lite website [https://vega.github.io/vega-lite/examples/line_conditional_axis.html])
- the Y axis configured with:
- hard-coded scale (in this case, 300 to 480)
- the X axis configured with:
- a “layer” block for the raw line, above reference area, below reference area, reference line, reference label
1 - Raw:
- a “line” mark
- this mark was legacy and used as a development aid and so the colour was set to transparent to display only the desired elements
- *** colour should be conditional base on a parameter selection, but this is left for future development ***
- this mark was legacy and used as a development aid and so the colour was set to transparent to display only the desired elements
2 - Above Reference:
- a nested “transform” block with:
- a “filter” transform to restrict the dataset to values equal to or above the reference value
- an “area” mark with:
- gradient colour (dark green) from the record value down to the reference line (using “y2” encoding set to the reference value)
- custom tooltip for reading date and formatted value (closing price)
3 - Below Reference:
- a nested “transform” block with:
- a “filter” transform to restrict the dataset to values equal to or below the reference value
- an “area” mark with:
- gradient colour (dark red) from the record value up to the reference line (using “y2” encoding set to the reference value)
- custom tooltip for reading date and formatted value (closing price)
4 - Reference Line:
- a “line” mark with a stroke width of 2
5 - Reference Label:
- a nested “transform” block with:
- a “calculate” transform to determine the display label (reference type and formatted reference value)
- a “filter” transform to restrict the dataset to a single record (the first reading [first date])
- a “text” mark at X-Y coordinates of first date-reference value
- (X and Y offsets are used to increase label clarity by slightly altering position)
Deneb/Vega-Lite JSON Code:
{
"title": {
"anchor": "start",
"align": "left",
"offset": -700,
"text": "Power BI Over/Under Plot using Deneb",
"font": "Verdana",
"fontSize": 24,
"fontWeight": "bold",
"fontStyle": "normal",
"subtitle": "Data Source: Microsoft Daily Close Stock Price, 07-Jun-2023 to 18-Oct-2024)",
"subtitleFont": "Verdana",
"subtitleFontSize": 16,
"subtitleFontWeight": "normal",
"subtitleFontStyle": "italic"
},
"padding": {
"left": 5,
"top": -655,
"right": 5,
"bottom": 5
},
"width": 1120,
"height": 440,
"data": {
"name": "dataset"
},
"transform": [
{
"joinaggregate": [
{
"op": "sum",
"field": "Counter",
"as": "_count_of_records"
}
]
},
{
"joinaggregate": [
{
"op": "mean",
"field": "Value",
"as": "_reference_value"
}
]
},
{
"window": [
{
"op": "lead",
"field": "ID",
"as": "_next_id"
},
{
"op": "lead",
"field": "Reading",
"as": "_next_reading"
},
{
"op": "lead",
"field": "Value",
"as": "_next_value"
}
]
},
{
"calculate": "datum['Reading'] == null ? null : datum['_next_value'] != null && datum['Value'] < datum['_reference_value'] && datum['_next_value'] > datum['_reference_value'] ? 'cross-up' : datum['_next_value'] != null && datum['Value'] > datum['_reference_value'] && datum['_next_value'] < datum['_reference_value'] ? 'cross-down' : datum['Value'] < datum['_reference_value'] ? 'below' : datum['Value'] > datum['_reference_value'] ? 'above' : '-999'",
"as": "_cross_type"
},
{
"calculate": "datum['_next_id'] - datum['ID']",
"as": "_diff_id"
},
{
"calculate": "datum['_next_reading'] - datum['Reading']",
"as": "_diff_reading"
},
{
"calculate": "datum['_next_value'] - datum['Value']",
"as": "_diff_value"
},
{
"calculate": "datum['_cross_type'] == 'cross-up' || datum['_cross_type'] == 'cross-down' ? ( datum['_reference_value'] - datum['Value'] ) / (datum['_next_value'] - datum['Value'] ) : null",
"as": "_increment_fraction"
},
{
"calculate": "datum['_increment_fraction'] != null ? datum['Reading'] + datum['_increment_fraction'] : null",
"as": "_new_reading"
},
{
// *** frame must be manually maintained (adjust hard-coded first value to equal negative number of records)
"window": [
{
"op": "first_value",
"field": "_new_reading",
"as": "_new_reading2"
}
],
"frame": [
-500,
0
]
},
{
"calculate": "datum['_new_reading2'] != null ? 'new' : datum['_cross_type']",
"as": "_cross_type"
},
{
"calculate": "datum['_new_reading2'] != null ? datum['_new_reading2'] : datum['Reading']",
"as": "Reading"
},
{
"calculate": "datum['_new_reading2'] != null ? datum['_reference_value'] : datum['Value']",
"as": "Value"
},
{
"calculate": "utcOffset('day', datetime(2023,5,6), datum['Reading'] )",
"as": "_reading_date"
}
],
"encoding": {
"x": {
"type": "temporal",
"axis": {
"title": "Date",
"titleFontSize": 16,
"labelFontSize": 12,
"tickCount": 12,
"labelAlign": "left",
"labelExpr": "[timeFormat(datum.value, '%b'), timeFormat(datum.value, '%m') == '01' ? timeFormat(datum.value, '%Y') : '']",
"labelOffset": 4,
"labelPadding": -24,
"tickSize": 30,
"gridColor": {
"condition": {
"test": {
"field": "value",
"timeUnit": "month",
"oneOf": [
1,
4,
7,
10
]
},
"value": "#969696"
},
"value": "#C9C9C9"
},
"tickColor": {
"condition": {
"test": {
"field": "value",
"timeUnit": "month",
"oneOf": [
1,
4,
7,
10
]
},
"value": "#969696"
},
"value": "#C9C9C9"
},
"gridDash": {
"condition": {
"test": {
"field": "value",
"timeUnit": "month",
"equal": 1
},
"value": []
},
"value": [
2,
2
]
},
"tickDash": {
"condition": {
"test": {
"field": "value",
"timeUnit": "month",
"equal": 1
},
"value": []
},
"value": [
2,
2
]
},
"gridWidth": {
"condition": {
"test": {
"field": "value",
"timeUnit": "month",
"oneOf": [
1,
4,
7,
10
]
},
"value": 2
},
"value": 1
},
"tickWidth": {
"condition": {
"test": {
"field": "value",
"timeUnit": "month",
"oneOf": [
1,
4,
7,
10
]
},
"value": 2
},
"value": 1
}
}
},
"y": {
"type": "quantitative",
"axis": {
"title": "Price",
"titleFontSize": 16,
"labelFontSize": 12
},
"scale": {
"domain": [
300,
480
]
}
}
},
"layer": [
{
"name": "RAW",
"mark": {
"type": "area",
"strokeWidth": 2,
"color": "transparent"
},
"encoding": {
"x": {
"field": "_reading_date"
},
"y": {
"field": "Value"
}
}
},
{
"name": "ABOVE_REFERENCE",
"transform": [
{
"filter": "datum['Value'] >= datum['_reference_value']"
},
{
"calculate": "utcOffset('day', datetime(2023,5,6), datum['Reading'] )",
"as": "_reading_date"
}
],
"mark": {
"type": "area",
"line": {
"color": "darkgreen"
},
"color": {
"x1": 1,
"y1": 1,
"x2": 1,
"y2": 0,
"gradient": "linear",
"stops": [
{
"offset": 0,
"color": "white"
},
{
"offset": 1,
"color": "darkgreen"
}
]
},
"tooltip": true
},
"encoding": {
"x": {
"field": "_reading_date"
},
"y": {
"field": "Value"
},
"y2": {
"field": "_reference_value"
},
"tooltip": [
{
"field": "_reading_date",
"type": "temporal",
"title": "Date"
},
{
"field": "Value",
"type": "quantitative",
"format": "0.2f",
"title": "Close"
}
]
}
},
{
"name": "BELOW_REFERENCE",
"transform": [
{
"filter": "datum['Value'] <= datum['_reference_value']"
},
{
"calculate": "utcOffset('day', datetime(2023,5,6), datum['Reading'] )",
"as": "_reading_date"
}
],
"mark": {
"type": "area",
"line": {
"color": "darkred"
},
"color": {
"x1": 1,
"y1": 0,
"x2": 1,
"y2": 1,
"gradient": "linear",
"stops": [
{
"offset": 0,
"color": "white"
},
{
"offset": 1,
"color": "darkred"
}
]
},
"tooltip": true
},
"encoding": {
"x": {
"field": "_reading_date"
},
"y": {
"field": "Value"
},
"y2": {
"field": "_reference_value"
},
"tooltip": [
{
"field": "_reading_date",
"type": "temporal",
"title": "Date"
},
{
"field": "Value",
"type": "quantitative",
"format": "0.2f",
"title": "Close"
}
]
}
},
{
"name": "REFERENCE",
"transform": [
{
"calculate": "utcOffset('day', datetime(2023,5,6), datum['Reading'] )",
"as": "_reading_date"
}
],
"mark": {
"type": "line",
"strokeWidth": 2,
"color": "#969696"
},
"encoding": {
"x": {
"field": "_reading_date"
},
"y": {
"field": "_reference_value"
}
}
},
{
"name": "REFERENCE_LABEL",
"transform": [
{
"calculate": "'Mean: ' + format( datum['_reference_value'], ',.2f' )",
"as": "_reference_label"
},
{
"filter": "datum['Reading'] == 1"
}
],
"mark": {
"type": "text",
"align": "left",
"fontSize": 14,
"fontWeight": "bold",
"fontStyle": "italic",
"xOffset": 10,
"yOffset": -10,
"color": "#969696"
},
"encoding": {
"text": {
"field": "_reference_label"
},
"x": {
"field": "_reading_date"
},
"y": {
"field": "_reference_value"
}
}
}
]
}
NOTE: This example is a proof-of-concept only, and a few shortcuts were taken to get the plot to render as desired. Items left for future development include:
- vertical position of the title, such that the [padding] block is not required
- vertical position of the visual, such that the Y-axis [scale} block values do not have an impact
- vertical position of screen widgets, such that parameters can be added for checkboxes for the visual part to display (raw line, above reference area, below reference area) and for options for reference values (mean, median, q1 [lower quartile boundary], q3 [upper quartile boundary], ci0 [lower boundary of the 95% confidence interval], ci1 [upper boundary of the 95% confidence interval])
- date responsiveness, such that a date-interval Power BI slicer can be added
Also included is the development sample PBIX using a datasource of Microsoft closing stock prices obtained from MarketWatch (https://www.marketwatch.com/investing/stock/MSFT/download-data) for the period June 7, 2023 to October 18, 2024; the dataset was extended with additional records as necessary to cover non-trading days (e.g., weekends, holidays, etc.) with the preceding days’ close price being carried over (500 rows).
The intent of this example was not to provide a finished visual, 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.
This example is provided as-is for information purposes only, and its use is solely at the discretion of the end user; no responsibility is assumed by the author.
Greg
Deneb Example - Over Under Plot - V10.pbix (2.3 MB)
