Deneb/Vega-Lite can extend a dataset. The example herein consists of two parts:
- (left) a standard scatterplot of reseller locations by sales amount and quantity
- (right) a scatterplot of reseller locations by sales amount and quantity, but extended with in-visual calculations of sales amount and quantity rankings; these rankings are then used to present the data in performance quadrants
This example illustrates a number of Deneb/Vega-Lite features, including:
STANDARD SCATTERPLOT:
- use of a “point” mark positioned at the sales amount and sales quantity values and sized by the sales amount
- use of the Power BI theme colours as enabled by Deneb (coloured by country)
- use of a custom tooltip with province & country, sales amount, and sales quantity
PERFORMANCE QUADRANTS:
0 - General:
- use of a “transform” block to extend the dataset with sales amount and quantity rankings and calculations of scaled sales amount and quantity values using the rankings (-5 to +5)
- use of a “params” block to set quadrant label and indicator colours
- use of a “layer” block consisting of 2 sections: 1x “point” (for the scaled ranking data), and 1x “layer” (filtered for the first row only), itself consisting of 10 sections: 2x “rule” (for the X and Y axes), 4x “text” (for the quadrant labels), and 4x “point” (for the quadrant indicators)
1 - Scaled Rankings:
- use of a “point” mark positioned at the scaled rankings and sized by the sales amount
- use of the Power BI theme colours as enabled by Deneb (coloured by country)
- use of a custom tooltip with province & country, sales amount, sales amount ranking, sales quantity, sales quantity ranking
2 - X and Y axes:
- use of a “rule” mark and y=0 for the X-axis (red colour)
- use of a “rule” mark and x=0 for the Y-axis (red colour)
3 - Quadrant Labels:
- use of 2x right-aligned “text” marks with hard-coded text and position values for the quadrant 1 & 2 labels
- use of 2x left-aligned “text” marks with hard-coded text and position values for the quadrant 3 & 4 labels
4 - Quadrant Indicators:
- use of 2x "point marks using the “triangle-up” symbol and hard-coded position values for the quadrant 1 & 3 indicators
- use of 2x "point marks using the “triangle-down” symbol and hard-coded position values for the quadrant 2 & 4 indicators
Deneb/Vega-Lite JSON Specification Code
{
"title": {
"anchor": "start",
"align": "left",
"text": "Power BI Performance Quadrants using Deneb",
"font": "Verdana",
"fontSize": 24,
"subtitle": "Reseller Locations by Sales and Quantity",
"subtitleFont": "Verdana",
"subtitleFontSize": 12,
"subtitleFontStyle": "italic"
},
"data": {"name": "dataset"},
"transform": [
{
"calculate": "datum['Province'] + ', ' + datum['Country']",
"as": "_province_country"
},
{
"window": [
{
"op": "rank",
"as": "_amount_rank"
}
],
"sort": [
{
"field": "Amount",
"order": "descending"
}
]
},
{
"window": [
{
"op": "rank",
"as": "_quantity_rank"
}
],
"sort": [
{
"field": "Quantity",
"order": "descending"
}
]
},
{
"window": [
{
"op": "count",
"as": "_row_count"
}
],
"frame": [null, null]
},
{
"calculate": "10 * ( ( datum['_row_count'] - datum['_amount_rank'] ) / datum['_row_count'] ) - 5",
"as": "_amount_scale"
},
{
"calculate": "10 * ( ( datum['_row_count'] - datum['_quantity_rank'] ) / datum['_row_count'] ) - 5",
"as": "_quantity_scale"
}
],
"params": [
{
"name": "_label_colour",
"value": "#969696"
},
{
"name": "_indicator_colour",
"value": "#4A4A4A"
}
],
"layer": [
{
"name": "RANK",
"mark": {
"type": "point",
"tooltip": true
},
"encoding": {
"x": {
"field": "_amount_scale",
"type": "quantitative",
"axis": null
},
"y": {
"field": "_quantity_scale",
"type": "quantitative",
"axis": null
},
"size": {
"field": "Amount",
"type": "quantitative"
},
"color": {
"field": "Country",
"type": "nominal",
"scale": {
"scheme": "pbiColorNominal"
}
},
"tooltip": [
{
"field": "_province_country",
"type": "nominal",
"title": "Location"
},
{
"field": "Amount",
"type": "quantitative",
"title": "Sales",
"formatType": "pbiFormat",
"format": "$#0,,.0M"
},
{
"field": "_amount_rank",
"type": "quantitative",
"title": "Sales Rank"
},
{
"field": "Quantity",
"type": "quantitative",
"formatType": "pbiFormat",
"format": "#0,.0K"
},
{
"field": "_quantity_rank",
"type": "quantitative",
"title": "Quantity Rank"
}
]
}
},
{
"transform": [
{
"filter": "datum['__row__'] == 0"
}
],
"layer": [
{
"name": "X-AXIS",
"mark": {"type": "rule"},
"encoding": {
"color": {"value": "red"},
"y": {"datum": 0}
}
},
{
"name": "Y-AXIS",
"mark": {"type": "rule"},
"encoding": {
"color": {"value": "red"},
"x": {"datum": 0}
}
},
{
"name": "QUADRANT_LABEL_1",
"mark": {
"type": "text",
"align": "right",
"baseline": "bottom",
"color": {
"expr": "_label_colour"
},
"yOffset": -15
},
"encoding": {
"text": {
"value": "HIGH AMOUNT/|HIGH QUANTITY"
},
"x": {"datum": 4.3}
}
},
{
"name": "QUADRANT_LABEL_2",
"mark": {
"type": "text",
"align": "right",
"baseline": "top",
"color": {
"expr": "_label_colour"
},
"yOffset": 4
},
"encoding": {
"text": {
"value": "HIGH AMOUNT/|LOW QUANTITY"
},
"x": {"datum": 4.3}
}
},
{
"name": "QUADRANT_LABEL_3",
"mark": {
"type": "text",
"align": "left",
"baseline": "bottom",
"color": {
"expr": "_label_colour"
},
"yOffset": -15
},
"encoding": {
"text": {
"value": "LOW AMOUNT/|HIGH QUANTITY"
},
"x": {"datum": -4.3}
}
},
{
"name": "QUADRANT_LABEL_4",
"mark": {
"type": "text",
"align": "left",
"baseline": "top",
"color": {
"expr": "_label_colour"
},
"yOffset": 4
},
"encoding": {
"text": {
"value": "LOW AMOUNT/|LOW QUANTITY"
},
"x": {"datum": -4.3}
}
},
{
"name": "QUADRANT_INDICATOR_1",
"mark": {
"type": "point",
"shape": "triangle-up",
"color": {
"expr": "_indicator_colour"
},
"size": 1000,
"yOffset": -16
},
"encoding": {
"x": {"datum": 4.7}
}
},
{
"name": "QUADRANT_INDICATOR_2",
"mark": {
"type": "point",
"shape": "triangle-down",
"color": {
"expr": "_indicator_colour"
},
"size": 1000,
"yOffset": 16
},
"encoding": {
"x": {"datum": 4.7}
}
},
{
"name": "QUADRANT_INDICATOR_3",
"mark": {
"type": "point",
"shape": "triangle-up",
"color": {
"expr": "_indicator_colour"
},
"size": 1000,
"yOffset": -16
},
"encoding": {
"x": {"datum": -4.7}
}
},
{
"name": "QUADRANT_INDICATOR_4",
"mark": {
"type": "point",
"shape": "triangle-down",
"color": {
"expr": "_indicator_colour"
},
"size": 1000,
"yOffset": 16
},
"encoding": {
"x": {"datum": -4.7}
}
}
]
}
]
}
The intent of this example is not to provide a finished visual, but rather to serve as a starting point for further custom visual development.
Also included is the development PBIX using data from the Microsoft SQL Server AdventureWorksDW2019 sample database (custom query used FactResellerSales, DimReseller and DimGeography tables).
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 Examples - Performance Quadrants.pbix (1.4 MB)