BOXPLOT OpenBI
A distribution's box in one row: count, quartiles, whiskers (1.5 IQR), mean and outliers, as R's quantile type 7 and ggplot2 give them.
Group: Statistics. OpenBI’s own: DAX has no function of this name.
Syntax
BOXPLOT ( <table>, <value> )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows whose values make the box. |
value | Expression evaluated for each row | The number worked out for each row. |
Returns
Table. One row. Count; the five-number summary Min, Q1, Median, Q3 and Max; Mean; Lower whisker and Upper whisker; and Outliers, how many values lie beyond the whiskers.
Remarks
- The quartiles are those of
PERCENTILE.INC: R's quantile of type 7, which ggplot2's box plots also use. Pick the columns you need withSELECTCOLUMNS, as the examples do. - Each whisker reaches the furthest value that is still within 1.5 times the box's height (Q3 − Q1) of the box. Values beyond them are outliers.
- Blank results are left out. With no value at all, Count and Outliers are 0 and the other columns blank.
- Text is an error. For one box per group, put it in
GENERATEwithCALCULATETABLE, as in the last example.
Compared with DAX
An OpenBI function: DAX has none like it, so a measure or query that uses it works in OpenBI only, not in Power BI or Analysis Services.
Examples
Ten delivery times, one of them late
45 minutes lies beyond the upper whisker at 22: one outlier.
EVALUATE
VAR Times =
DATATABLE (
"Minutes", INTEGER,
{ { 12 }, { 14 }, { 15 }, { 15 }, { 16 }, { 18 }, { 19 }, { 21 }, { 22 }, { 45 } }
)
RETURN
SELECTCOLUMNS (
BOXPLOT ( Times, [Minutes] ),
"Count", [Count], "Min", [Min], "Q1", [Q1], "Median", [Median], "Q3", [Q3], "Max", [Max],
"Mean", [Mean], "Lower whisker", [Lower whisker], "Upper whisker", [Upper whisker],
"Outliers", [Outliers]
)| Count | Min | Q1 | Median | Q3 | Max | Mean | Lower whisker | Upper whisker | Outliers |
|---|---|---|---|---|---|---|---|---|---|
| 10 | 12 | 15 | 17 | 20.5 | 45 | 19.7 | 12 | 22 | 1 |
Crates per place
The depot's 0 crates is the outlier; the lower whisker stops at the smallest shop, 7.
EVALUATE
SELECTCOLUMNS (
BOXPLOT ( Places, Places[Crates] ),
"Lower whisker", [Lower whisker],
"Q1", [Q1],
"Median", [Median],
"Q3", [Q3],
"Upper whisker", [Upper whisker],
"Outliers", [Outliers]
)| Lower whisker | Q1 | Median | Q3 | Upper whisker | Outliers |
|---|---|---|---|---|---|
| 7 | 9 | 11.5 | 14 | 18 | 1 |
A box per product, of its months
EVALUATE
SELECTCOLUMNS (
GENERATE (
VALUES ( Products[Product] ),
CALCULATETABLE ( BOXPLOT ( VALUES ( Sales[Month] ), [Liters sold] ) )
),
"Product", Products[Product],
"Q1", [Q1],
"Median", [Median],
"Q3", [Q3],
"Outliers", [Outliers]
)
ORDER BY [Product]| Product | Q1 | Median | Q3 | Outliers |
|---|---|---|---|---|
| Cider | 7511 | 7840 | 8851.5 | 2 |
| IPA | 8200 | 8632 | 9814 | 1 |
| Lettøl | 4249.5 | 4445.5 | 4973.5 | 1 |
| Pils | 24383.5 | 26964 | 30817 | 0 |