In statistics, a z-score tells us how many standard deviations away a value lies from the mean.
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)
This will create a new column named Z Score that contains the z-score for the corresponding value in the Points column:
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: How to Interpret Z-Scores (With Examples)
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI:
How to Calculate Average in Power BI
How to Calculate Standard Deviation in Power BI
How to Rank Values by Group in Power BI
