Table of Contents
Z-scores, also known as standard scores, are a statistical measurement that indicates how many standard deviations a data point is above or below the mean of a dataset. In Power BI, calculating Z-scores can be done using the “Z-Score” function, which takes the value of a data point, the mean of the dataset, and the standard deviation as inputs. This function can be applied to any numerical column in a dataset, allowing users to easily identify and analyze data points that are significantly different from the mean. By calculating Z-scores in Power BI, users can gain valuable insights and make informed decisions based on the distribution and variability of their data.
Calculate Z-Scores in Power BI
In statistics, a z-score tells us how many standard deviations away a value lies from the .
We use the following formula to calculate a z-score:
z = (x – μ) / σ
where:
- x is a single raw data value
- μ is the population mean
- σ is the population standard deviation
To calculate z-scores in Power BI, you can use the following syntax in DAX:
Z Score =
VAR Xi = 'my_data'[Points]
VAR MeanValue = AVERAGE('my_data'[Points])
VAR StDevValue = STDEV.P('my_data'[Points])
RETURNDIVIDE(Xi - MeanValue, StDevValue)
This particular formula will create a new column named Z Score that contains the z-score of each value from the Points column in the table named my_data.
The following example shows how to use this formula in practice.
Example: How to Calculate Z-Scores in Power BI
Suppose we have the following table named my_data in Power BI that contains information about points scored by basketball players on various teams:
Suppose that we would like to calculate the z-score for each value in the Points column.
To do so, click the Table tools tab and then click the New column icon:
Then type the following formula into the formula bar:
Z Score =
VAR Xi = 'my_data'[Points]
VAR MeanValue = AVERAGE('my_data'[Points])
VAR StDevValue = STDEV.P('my_data'[Points])
RETURNDIVIDE(Xi - MeanValue, StDevValue)
Here is how to interpret the values in the Z Score column:
- The first points value of 22 is 0.1788 standard deviations below the mean points value.
- The second points value of 14 is 1.2005 standard deviations below the mean points value.
- The third points value of 18 is 0.6897 standard deviations below the mean points value.
- The fourth points value of 39 is 1.9924 standard deviations above the mean points value.
And so on.
Related:
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: