Deneb/Vega-Lite can be used to create facetted histograms showing not only the “part” (category) but also the “whole” (total).
I first saw an example of this a few months ago in a post by Joachim Schork showcasing some ggplot2 extensions.
I wanted to explore the presentation of binned data in Deneb/Vega-Lite using a small helper table (30 rows; 3 categories x 10 bins), a small dataset (344 rows; Vega/Vega-Lite penguins sample), and simple DAX (2 measures).
Species Rows =
VAR _Species = SELECTEDVALUE( 'Species Bins'[Species] )
VAR _Min = SELECTEDVALUE( 'Species Bins'[Bin Min] )
VAR _Max = SELECTEDVALUE( 'Species Bins'[Bin Max] )
VAR _Result = CALCULATE(
COUNTROWS( Penguins ),
FILTER(
Penguins,
Penguins[Species] = _Species &&
Penguins[Body Mass (g)] >= _Min &&
Penguins[Body Mass (g)] <= _Max
)
) + 0
RETURN
_Result
Total Rows =
VAR _Min = SELECTEDVALUE( 'Species Bins'[Bin Min] )
VAR _Max = SELECTEDVALUE( 'Species Bins'[Bin Max] )
VAR _Result = CALCULATE(
COUNTROWS( Penguins ),
FILTER(
Penguins,
Penguins[Body Mass (g)] >= _Min &&
Penguins[Body Mass (g)] <= _Max
)
) + 0
RETURN
_Result
The example herein presents histograms of penguin count by body mass facetted by species above an overall total penguin count; a custom tooltip shows basic and calculated information.
This example illustrates a number of Deneb/Vega-Lite features, including:
0 - General:
- a “title” block with subtitle
- a “facet” block to produce separate species visuals
- a “spec” block containing a “layer” block to display the species count above the total count
1 - Total:
- a “bar” mark in medium grey colour
- “x” axis encoding using the single-line binning provided by Vega-Lite
2 - Species:
- a nested “transform” block with:
- 2x “calculate” transforms to compose the bin range and to determine the species/total value
- a “bar” mark with:
- 75% opacity
- “x” axis encoding using the single-line binning provided by Vega-Lite
- “x” axis scaling to produce 500g bins and allow empty bins on each end
- conditional colour by species
- a custom tooltip with species, bin range, species rows, total rows, and species/total percent
Deneb/Vega-Lite JSON Code:
{
"title": {
"anchor": "start",
"align": "left",
"offset": 20,
"text": "Power BI Facetted Historgrams (Category vs. Total) using Deneb",
"font": "Segoe UI",
"fontSize": 32,
"fontWeight": "bold",
"fontStyle": "normal",
"subtitle": "Data Source: Vega/Vega-Lite sample data - Penguins dataset",
"subtitleFont": "Segoe UI",
"subtitleFontSize": 18,
"subtitleFontWeight": "normal",
"subtitleFontStyle": "italic"
},
"data": {
"name": "dataset"
},
"facet": {
"column": {
"field": "Species",
"header": {
"labelFontSize": 24,
"titleFontSize": 18
}
}
},
"spec": {
"width": 350,
"height": 580,
"layer": [
{
"name": "TOTAL",
"mark": {
"type": "bar",
"color": "#C9C9C9"
},
"encoding": {
"x": {
"field": "Bin Min",
"bin": {
"binned": true,
"step": 500
}
},
"x2": {
"field": "Bin Max"
},
"y": {
"field": "Total Rows",
"type": "quantitative"
}
}
},
{
"name": "SPECIES",
"transform": [
{
"calculate": "datum['Bin Min'] + '-' + datum['Bin Max']",
"as": "_bin_range"
},
{
"calculate": "datum['Species Rows'] / datum['Total Rows']",
"as": "_species_percent"
}
],
"mark": {
"type": "bar",
"opacity": 0.75,
"tooltip": true
},
"encoding": {
"x": {
"field": "Bin Min",
"bin": {
"binned": true,
"step": 500
},
"axis": {
"title": "Body Mass (g)",
"labelFontSize": 12,
"titleFontSize": 14
},
"scale": {
"domain": [
2000,
7000
]
}
},
"x2": {
"field": "Bin Max"
},
"y": {
"field": "Species Rows",
"type": "quantitative",
"axis": {
"title": "Count",
"labelFontSize": 12,
"titleFontSize": 14
}
},
"color": {
"field": "Species",
"type": "nominal",
"scale": {
"range": [
"#0F4C81", // blue
"#1E5E08", // green
"#B71021" // red
]
},
"legend": null
},
"tooltip": [
{
"field": "Species",
"type": "nominal"
},
{
"field": "_bin_range",
"type": "nominal",
"title": "Bin"
},
{
"field": "Species Rows",
"type": "quantitative",
"title": "Species Rows"
},
{
"field": "Total Rows",
"type": "quantitative",
"title": "Total Rows"
},
{
"field": "_species_percent",
"type": "quantitative",
"title": "Percent of Total",
"format": "0.0%"
}
]
}
}
]
}
}
Also included is the development sample PBIX using, as noted above, Vega/Vega-Lite sample data.
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 a starting point 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 - Facetted Histograms - V5.pbix (5.2 MB)