Calculated Column with aggregate window partition

Hi DoctorYSG ,

Z-Score with Partitioning in a Calculated Column:
Assuming your table is 'App Stats' and the relevant columns are below

WaitTimeMS (numeric value)
AppName, AppVersion, BaseCode, DeviceName (partitioning columns)

Below is to write DAX expression for the calculated column:

ZScore_Partitioned :=
VAR CurrentApp = 'App Stats'[AppName]
VAR CurrentVersion = 'App Stats'[AppVersion]
VAR CurrentBase = 'App Stats'[BaseCode]
VAR CurrentDevice = 'App Stats'[DeviceName]
VAR CurrentWait = 'App Stats'[WaitTimeMS]

VAR Mean =
CALCULATE(
AVERAGE('App Stats'[WaitTimeMS]),
FILTER(
'App Stats',
'App Stats'[AppName] = CurrentApp &&
'App Stats'[AppVersion] = CurrentVersion &&
'App Stats'[BaseCode] = CurrentBase &&
'App Stats'[DeviceName] = CurrentDevice
)
)

VAR StdDev =
CALCULATE(
STDEV.P('App Stats'[WaitTimeMS]),
FILTER(
'App Stats',
'App Stats'[AppName] = CurrentApp &&
'App Stats'[AppVersion] = CurrentVersion &&
'App Stats'[BaseCode] = CurrentBase &&
'App Stats'[DeviceName] = CurrentDevice
)
)

RETURN
DIVIDE(CurrentWait - Mean, StdDev, 0)

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X