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?

1 Reply

  • 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