HISTOGRAM OpenBI
Sorts an expression's values into bins and counts them, one row a bin: at round break points by default, or into a given number of bins of equal width.
Group: Statistics. OpenBI’s own: DAX has no function of this name.
Syntax
HISTOGRAM ( <table>, <value> [, <bins>] )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows to count. |
value | Expression evaluated for each row | The number worked out for each row. |
bins | Whole number optional | How many bins of equal width, 1 or more. Left out, the bins are chosen for you. |
Returns
Table. A row a bin, in order: Bin start, Bin end, Count, and Share (the bin's count over all the values counted).
Remarks
- Without bins, the bins are R's hist: the number aimed at is Sturges', 1 + log2 n rounded up for n values, and the break points are round numbers as R's pretty picks them: steps such as 0.5, 1, 2, 5, 10 or 20.
- With bins, the range from the smallest value to the largest is cut into that many equal parts.
- A bin counts the values above its start up to its end, its end included; the first bin includes its start too.
- Empty bins are rows with a count of 0, so the bins follow on without gaps.
- Blank results are left out. When every value is the same, there is one bin, 1 wide, around it.
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
For ten values Sturges' rule aims at five bins; the round step R picks is 5 minutes, and seven bins of it cover 12 to 45, from 10. Both 15s fall in the first bin, as its end. R's hist(c(12, 14, 15, 15, 16, 18, 19, 21, 22, 45)) gives the same breaks and counts.
EVALUATE
VAR Times =
DATATABLE (
"Minutes", INTEGER,
{ { 12 }, { 14 }, { 15 }, { 15 }, { 16 }, { 18 }, { 19 }, { 21 }, { 22 }, { 45 } }
)
RETURN
HISTOGRAM ( Times, [Minutes] )| Bin start | Bin end | Count | Share |
|---|---|---|---|
| 10 | 15 | 4 | 0.4 |
| 15 | 20 | 3 | 0.3 |
| 20 | 25 | 2 | 0.2 |
| 25 | 30 | 0 | 0 |
| 30 | 35 | 0 | 0 |
| 35 | 40 | 0 | 0 |
| 40 | 45 | 1 | 0.1 |
Three bins of equal width
EVALUATE
VAR Times =
DATATABLE (
"Minutes", INTEGER,
{ { 12 }, { 14 }, { 15 }, { 15 }, { 16 }, { 18 }, { 19 }, { 21 }, { 22 }, { 45 } }
)
RETURN
HISTOGRAM ( Times, [Minutes], 3 )| Bin start | Bin end | Count | Share |
|---|---|---|---|
| 12 | 23 | 9 | 0.9 |
| 23 | 34 | 0 | 0 |
| 34 | 45 | 1 | 0.1 |
Months by liters sold
EVALUATE
HISTOGRAM ( VALUES ( Sales[Month] ), [Liters sold] )| Bin start | Bin end | Count | Share |
|---|---|---|---|
| 40000 | 45000 | 10 | 0.277777777777778 |
| 45000 | 50000 | 13 | 0.361111111111111 |
| 50000 | 55000 | 4 | 0.111111111111111 |
| 55000 | 60000 | 5 | 0.138888888888889 |
| 60000 | 65000 | 3 | 0.0833333333333333 |
| 65000 | 70000 | 1 | 0.0277777777777778 |