Forum Discussion

iDataDrew's avatar
iDataDrew
Icon for Advocate IV rankAdvocate IV
9 years ago

How to duplicate the Excel STANDARDIZE() function in DAX to generate normalized ratio?

I'm trying to duplicate the STANDARDIZE() function available in Exel in order to generate a standardized ratio, or z-score.   z= (x - m) / sd   How would I write a formula for this?
dkay84_PowerBI's avatar
dkay84_PowerBI
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

I'm assuming you have a data table that has the X and SD values, and you can calculate the population mean from the data?

 

I would create a measure in the following form:

 

Zscore = 

 

var popMean = calculate(average(Table[Value]),all(Table)),

 

return (table[Value] - popMean) / table[SD]

 

Note: you could calculate the SD if necessary using another variable like popMean