CORREL.SPEARMAN OpenBI
The rank correlation between two expressions over a table's rows (Spearman's; ties share their average rank).
Group: Statistics. OpenBI’s own: DAX has no function of this name.
Syntax
CORREL.SPEARMAN ( <table>, <x>, <y> )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows to go through. |
x | Expression evaluated for each row | The first number, worked out for each row. |
y | Expression evaluated for each row | The second number, worked out for each row. |
Returns
Number. The Spearman rank correlation between x and y, from −1 to 1.
Remarks
- Each value is replaced by its rank among its own column's values, and Pearson's correlation of the ranks is the answer. Tied values share the mean of their ranks.
- It is 1 whenever y always rises with x, however unevenly, and it is hardly moved by one extreme value.
- Rows with a blank in x or y are left out; with fewer than two rows, or no variation, the result is BLANK.
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
A steady rise that is not a line
y is x squared, with one very large last value: Pearson's coefficient is well below 1, Spearman's is exactly 1.
EVALUATE
VAR Pairs =
DATATABLE (
"x", INTEGER, "y", INTEGER,
{ { 1, 1 }, { 2, 4 }, { 3, 9 }, { 4, 16 }, { 5, 100 } }
)
RETURN
ROW (
"Pearson", CORREL ( Pairs, [x], [y] ),
"Spearman", CORREL.SPEARMAN ( Pairs, [x], [y] )
)| Pearson | Spearman |
|---|---|
| 0.795203573829603 | 1 |
Ties share their rank
The two grades of 2 both rank 1.5.
EVALUATE
VAR Marks =
DATATABLE (
"Score", INTEGER, "Grade", INTEGER,
{ { 1, 2 }, { 2, 2 }, { 3, 3 }, { 4, 5 }, { 5, 4 } }
)
RETURN
ROW ( "Spearman", CORREL.SPEARMAN ( Marks, [Score], [Grade] ) )| Spearman |
|---|
| 0.872081599272381 |
Population and GDP by continent, both ways
EVALUATE
SUMMARIZECOLUMNS (
Countries[Continent],
"Pearson", CORREL ( Countries, Countries[People], Countries[GDP] ),
"Spearman", CORREL.SPEARMAN ( Countries, Countries[People], Countries[GDP] )
)
ORDER BY Countries[Continent]| Countries[Continent] | Pearson | Spearman |
|---|---|---|
| Africa | 0.820871122295425 | 0.799276018099548 |
| Asia | 0.781768427189445 | 0.753700277520814 |
| Europe | 0.792967830668549 | 0.838056680161943 |
| North America | 0.950765041991018 | 0.737874097007224 |
| Oceania | 0.948472070437285 | 0.75 |
| South America | 0.983989902077897 | 0.934065934065934 |