Forum Discussion
Double box plot
Hello, I am trying to find a graph or a solution to have a double box plot. See picture. Anyone?
Thanks a lot,
Jim
Hi Anonymous, and thanks for such an interesting question! I'm not aware of anything, so I have had a go at building using Deneb with Vega-Lite and the iris dataset, which I hope proves the concept:
While Vega-Lite has a boxplot mark for standard box plots, I needed to calculate the statistics and draw this from primitive marks, so the specification is quite long. I've attached a workbook showing this in action, and I'll list the JSON specification lower down.
Note that because Power BI aggregates measures, you will need to use an index row to ensure that you get all rows in your dataset as intended. In the workbook, this is the Row Number field, and is a sequential field.
If you're not familiar with this concept, I have a blog post here about it, which may help.
Note that this solution is provided as-is, as it took me a few hours to work out, so don't have any time available for further support. If you want to tweak this design, then it's best to have a look at the Vega-Lite documentation to see how the language works. Because Deneb visuals are certified you get many of the advantages you don't get with R and Python visuals, such as full compatibility with publish to web, and PowerPoint & PDF exports.
Cheers,
Daniel
JSON Specification (as per workbook):
{ "data": {"name": "dataset"}, "transform": [ { "joinaggregate": [ { "op": "count", "field": "Row Number", "as": "count" }, { "op": "q1", "field": "Sepal Length", "as": "x_q1" }, { "op": "q3", "field": "Sepal Length", "as": "x_q3" }, { "op": "median", "field": "Sepal Length", "as": "x_median" }, { "op": "min", "field": "Sepal Length", "as": "x_min" }, { "op": "max", "field": "Sepal Length", "as": "x_max" }, { "op": "q1", "field": "Sepal Width", "as": "y_q1" }, { "op": "q3", "field": "Sepal Width", "as": "y_q3" }, { "op": "median", "field": "Sepal Width", "as": "y_median" }, { "op": "min", "field": "Sepal Width", "as": "y_min" }, { "op": "max", "field": "Sepal Width", "as": "y_max" } ], "groupby": ["Species"] }, { "calculate": "datum['x_q3'] - datum['x_q1']", "as": "x_iqr" }, { "calculate": "datum['y_q3'] - datum['y_q1']", "as": "y_iqr" }, { "calculate": "datum['x_q1'] - 1.5 * datum['x_iqr']", "as": "x_lower" }, { "calculate": "datum['x_q3'] + 1.5 * datum['x_iqr']", "as": "x_upper" }, { "calculate": "datum['y_q1'] - 1.5 * datum['y_iqr']", "as": "y_lower" }, { "calculate": "datum['y_q3'] + 1.5 * datum['y_iqr']", "as": "y_upper" }, { "calculate": "datum['Sepal Length'] < datum['x_lower'] || datum['Sepal Length'] > datum['x_upper']", "as": "x_outlier" }, { "calculate": "datum['Sepal Width'] < datum['y_lower'] || datum['Sepal Width'] > datum['y_upper']", "as": "y_outlier" }, { "calculate": "'x=' + pbiFormat(datum['x_min'], '#0.0') + ', y=' + pbiFormat(datum['y_min'], '#0.0')", "as": "tooltip_min" }, { "calculate": "'x=' + pbiFormat(datum['x_q1'], '#0.0') + ', y=' + pbiFormat(datum['y_q1'], '#0.0')", "as": "tooltip_q1" }, { "calculate": "'x=' + pbiFormat(datum['x_median'], '#0.0') + ', y=' + pbiFormat(datum['y_median'], '#0.0')", "as": "tooltip_median" }, { "calculate": "'x=' + pbiFormat(datum['x_q3'], '#0.0') + ', y=' + pbiFormat(datum['y_q3'], '#0.0')", "as": "tooltip_q3" }, { "calculate": "'x=' + pbiFormat(datum['x_max'], '#0.0') + ', y=' + pbiFormat(datum['y_max'], '#0.0')", "as": "tooltip_max" } ], "layer": [ { "description": "Box plot portion; aggregate our data so that we only get the right number of marks per category.", "transform": [ { "aggregate": [], "groupby": [ "Species", "count", "x_min", "x_lower", "x_q1", "x_median", "x_q3", "x_upper", "x_max", "y_min", "y_lower", "y_q1", "y_median", "y_q3", "y_upper", "y_max", "tooltip_min", "tooltip_q1", "tooltip_median", "tooltip_q3", "tooltip_max" ] } ], "layer": [ { "description": "Box (drawn with a rectangle and the x/y quartiles.", "mark": { "type": "rect", "tooltip": true, "fillOpacity": 0.5 }, "encoding": { "x": {"field": "x_q1"}, "x2": {"field": "x_q3"}, "y": {"field": "y_q1"}, "y2": {"field": "y_q3"}, "stroke": { "field": "Species", "legend": null } } }, { "description": "x whisker (runs from x lower to x upper and positioned vertically by y median).", "mark": {"type": "rule"}, "encoding": { "x": {"field": "x_lower"}, "x2": {"field": "x_upper"}, "y": {"field": "y_median"} } }, { "description": "x lower and upper rules", "encoding": { "y": {"field": "y_q3"}, "y2": {"field": "y_q1"} }, "layer": [ { "description": "x lower value", "mark": {"type": "rule"}, "encoding": { "x": { "field": "x_lower" } } }, { "description": "x upper value", "mark": {"type": "rule"}, "encoding": { "x": { "field": "x_upper" } } } ] }, { "description": "y whisker (runs from y lower to y upper and positioned vertically by x median).", "mark": {"type": "rule"}, "encoding": { "y": {"field": "y_lower"}, "y2": {"field": "y_upper"}, "x": {"field": "x_median"} } }, { "description": "y lower and upper rules", "encoding": { "x": {"field": "x_q3"}, "x2": {"field": "x_q1"} }, "layer": [ { "description": "y lower value", "mark": {"type": "rule"}, "encoding": { "y": { "field": "y_lower" } } }, { "description": "y upper value", "mark": {"type": "rule"}, "encoding": { "y": { "field": "y_upper" } } } ] } ] }, { "description": "outlier values.", "encoding": { "tooltip": [ {"field": "Species"}, {"field": "Sepal Width"}, {"field": "Sepal Length"} ] }, "layer": [ { "description": "x outlier values.", "transform": [ { "filter": "datum['x_outlier']" } ], "mark": { "type": "point", "tooltip": true, "filled": false }, "encoding": { "x": { "field": "Sepal Length" }, "y": {"field": "y_median"} } }, { "description": "y outlier values.", "transform": [ { "filter": "datum['y_outlier']" } ], "mark": { "type": "point", "filled": false, "tooltip": true }, "encoding": { "y": { "field": "Sepal Width" }, "x": {"field": "x_median"} } } ] } ], "encoding": { "x": { "type": "quantitative", "scale": {"zero": false}, "axis": {"title": "Sepal Length"} }, "y": { "type": "quantitative", "scale": {"zero": false}, "axis": {"title": "Sepal Width"} }, "color": {"field": "Species"}, "tooltip": [ {"field": "Species"}, { "field": "tooltip_min", "title": "Minimum Value" }, { "field": "tooltip_q1", "title": "Lower Quartile" }, { "field": "tooltip_median", "title": "Median" }, { "field": "tooltip_q3", "title": "Upper Quartile" }, { "field": "tooltip_max", "title": "Maximum Value" }, { "field": "count", "title": "# Samples" } ] } }
6 Replies
Hi Anonymous, and thanks for such an interesting question! I'm not aware of anything, so I have had a go at building using Deneb with Vega-Lite and the iris dataset, which I hope proves the concept:
While Vega-Lite has a boxplot mark for standard box plots, I needed to calculate the statistics and draw this from primitive marks, so the specification is quite long. I've attached a workbook showing this in action, and I'll list the JSON specification lower down.
Note that because Power BI aggregates measures, you will need to use an index row to ensure that you get all rows in your dataset as intended. In the workbook, this is the Row Number field, and is a sequential field.
If you're not familiar with this concept, I have a blog post here about it, which may help.
Note that this solution is provided as-is, as it took me a few hours to work out, so don't have any time available for further support. If you want to tweak this design, then it's best to have a look at the Vega-Lite documentation to see how the language works. Because Deneb visuals are certified you get many of the advantages you don't get with R and Python visuals, such as full compatibility with publish to web, and PowerPoint & PDF exports.
Cheers,
Daniel
JSON Specification (as per workbook):
{ "data": {"name": "dataset"}, "transform": [ { "joinaggregate": [ { "op": "count", "field": "Row Number", "as": "count" }, { "op": "q1", "field": "Sepal Length", "as": "x_q1" }, { "op": "q3", "field": "Sepal Length", "as": "x_q3" }, { "op": "median", "field": "Sepal Length", "as": "x_median" }, { "op": "min", "field": "Sepal Length", "as": "x_min" }, { "op": "max", "field": "Sepal Length", "as": "x_max" }, { "op": "q1", "field": "Sepal Width", "as": "y_q1" }, { "op": "q3", "field": "Sepal Width", "as": "y_q3" }, { "op": "median", "field": "Sepal Width", "as": "y_median" }, { "op": "min", "field": "Sepal Width", "as": "y_min" }, { "op": "max", "field": "Sepal Width", "as": "y_max" } ], "groupby": ["Species"] }, { "calculate": "datum['x_q3'] - datum['x_q1']", "as": "x_iqr" }, { "calculate": "datum['y_q3'] - datum['y_q1']", "as": "y_iqr" }, { "calculate": "datum['x_q1'] - 1.5 * datum['x_iqr']", "as": "x_lower" }, { "calculate": "datum['x_q3'] + 1.5 * datum['x_iqr']", "as": "x_upper" }, { "calculate": "datum['y_q1'] - 1.5 * datum['y_iqr']", "as": "y_lower" }, { "calculate": "datum['y_q3'] + 1.5 * datum['y_iqr']", "as": "y_upper" }, { "calculate": "datum['Sepal Length'] < datum['x_lower'] || datum['Sepal Length'] > datum['x_upper']", "as": "x_outlier" }, { "calculate": "datum['Sepal Width'] < datum['y_lower'] || datum['Sepal Width'] > datum['y_upper']", "as": "y_outlier" }, { "calculate": "'x=' + pbiFormat(datum['x_min'], '#0.0') + ', y=' + pbiFormat(datum['y_min'], '#0.0')", "as": "tooltip_min" }, { "calculate": "'x=' + pbiFormat(datum['x_q1'], '#0.0') + ', y=' + pbiFormat(datum['y_q1'], '#0.0')", "as": "tooltip_q1" }, { "calculate": "'x=' + pbiFormat(datum['x_median'], '#0.0') + ', y=' + pbiFormat(datum['y_median'], '#0.0')", "as": "tooltip_median" }, { "calculate": "'x=' + pbiFormat(datum['x_q3'], '#0.0') + ', y=' + pbiFormat(datum['y_q3'], '#0.0')", "as": "tooltip_q3" }, { "calculate": "'x=' + pbiFormat(datum['x_max'], '#0.0') + ', y=' + pbiFormat(datum['y_max'], '#0.0')", "as": "tooltip_max" } ], "layer": [ { "description": "Box plot portion; aggregate our data so that we only get the right number of marks per category.", "transform": [ { "aggregate": [], "groupby": [ "Species", "count", "x_min", "x_lower", "x_q1", "x_median", "x_q3", "x_upper", "x_max", "y_min", "y_lower", "y_q1", "y_median", "y_q3", "y_upper", "y_max", "tooltip_min", "tooltip_q1", "tooltip_median", "tooltip_q3", "tooltip_max" ] } ], "layer": [ { "description": "Box (drawn with a rectangle and the x/y quartiles.", "mark": { "type": "rect", "tooltip": true, "fillOpacity": 0.5 }, "encoding": { "x": {"field": "x_q1"}, "x2": {"field": "x_q3"}, "y": {"field": "y_q1"}, "y2": {"field": "y_q3"}, "stroke": { "field": "Species", "legend": null } } }, { "description": "x whisker (runs from x lower to x upper and positioned vertically by y median).", "mark": {"type": "rule"}, "encoding": { "x": {"field": "x_lower"}, "x2": {"field": "x_upper"}, "y": {"field": "y_median"} } }, { "description": "x lower and upper rules", "encoding": { "y": {"field": "y_q3"}, "y2": {"field": "y_q1"} }, "layer": [ { "description": "x lower value", "mark": {"type": "rule"}, "encoding": { "x": { "field": "x_lower" } } }, { "description": "x upper value", "mark": {"type": "rule"}, "encoding": { "x": { "field": "x_upper" } } } ] }, { "description": "y whisker (runs from y lower to y upper and positioned vertically by x median).", "mark": {"type": "rule"}, "encoding": { "y": {"field": "y_lower"}, "y2": {"field": "y_upper"}, "x": {"field": "x_median"} } }, { "description": "y lower and upper rules", "encoding": { "x": {"field": "x_q3"}, "x2": {"field": "x_q1"} }, "layer": [ { "description": "y lower value", "mark": {"type": "rule"}, "encoding": { "y": { "field": "y_lower" } } }, { "description": "y upper value", "mark": {"type": "rule"}, "encoding": { "y": { "field": "y_upper" } } } ] } ] }, { "description": "outlier values.", "encoding": { "tooltip": [ {"field": "Species"}, {"field": "Sepal Width"}, {"field": "Sepal Length"} ] }, "layer": [ { "description": "x outlier values.", "transform": [ { "filter": "datum['x_outlier']" } ], "mark": { "type": "point", "tooltip": true, "filled": false }, "encoding": { "x": { "field": "Sepal Length" }, "y": {"field": "y_median"} } }, { "description": "y outlier values.", "transform": [ { "filter": "datum['y_outlier']" } ], "mark": { "type": "point", "filled": false, "tooltip": true }, "encoding": { "y": { "field": "Sepal Width" }, "x": {"field": "x_median"} } } ] } ], "encoding": { "x": { "type": "quantitative", "scale": {"zero": false}, "axis": {"title": "Sepal Length"} }, "y": { "type": "quantitative", "scale": {"zero": false}, "axis": {"title": "Sepal Width"} }, "color": {"field": "Species"}, "tooltip": [ {"field": "Species"}, { "field": "tooltip_min", "title": "Minimum Value" }, { "field": "tooltip_q1", "title": "Lower Quartile" }, { "field": "tooltip_median", "title": "Median" }, { "field": "tooltip_q3", "title": "Upper Quartile" }, { "field": "tooltip_max", "title": "Maximum Value" }, { "field": "count", "title": "# Samples" } ] } }giammariam Hello to you. I had made a request to have this kind of visual. The graph seems to correspond completely to what I asked for. but when I go to see in the code, there is much too specific information that cannot answer a standard request according to what I understand. 1. I have different tables that must go in this graph. Here I see that it is indicated: "dataset". Is that a problem? 2. I see "datum" : is that a constraint? 3. I also see that we are talking about Q1, Q3. I dont understand if I need this type of data. To be more clear, is it possible to have a much more generic code. Standard? I am not sure to understand how i can use it with my data in fact. this is not clear for me... and also if I have twenty box plots, will the size change? And if the size doesn't change, is it possible to add the size adjustment bar that you created for the other graphs? Thank you very much. This one is super important to me.
Hey Jim. While I certainly appreciate the tag, I'm a bit confused. Did you mean to tag dm-p in this one? Seeing as he was the author of the solution (not to mention the creator of Deneb), he is far more fit to help you with this than I am. If I'm missing something and you still need support, let me know.
