How to Calculate Z-Scores in Power BI


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:

Power BI z-score

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

Leave a Reply

Your email address will not be published. Required fields are marked *