Table of Contents
Z-Scores are a measure of how many standard deviations a particular data point is above or below the mean of a data set. In Power BI, Z-Scores can be calculated using the DAX function STDEV.P to calculate the standard deviation of the data set, and the AVERAGE function to calculate the mean. Then, the Z-Score can be calculated by subtracting the data point from the mean and dividing by the standard deviation. This allows for a standardized comparison of data points within a data set, helping to identify outliers and assess the overall distribution of the data.
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])
RETURN DIVIDE(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])
RETURN DIVIDE(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: