Forum Discussion
Calculated Column with aggregate window partition
In this article: How to Calculate Z-Scores in Power BI
He shows how to create Z-Scores with an calculated column. Very simple, But the STDDEV and AVERAGES are based on all rows of the table. I want to use a window partion and create the mean and std for only rows that have the same computer device name, same applicaiton, same location, etc.
So instead of:
```
zzTokenWait =
VAR data = 'App Stats'[WaitTimeMS]
VAR mean = AVERAGE('App Stats'[WaitTimeMS])
VAR std = STDEV.P('App Stats'[WaitTimeMS])
RETURN DIVIDE(data - mean, std, 0)VAR mean = CALCULATE(
AVERAGE('App Stats'[WaitTimeMS]),
FILTER(
WINDOW(
1,
ABS,
-1,
ABS,
'App Stats',
ORDERBY('App Stats'[TimeStamp]),
DEFAULT,
PARTITIONBY(
'App Stats'[AppName],
'App Stats'[AppVersion],
'App Stats'[BaseCode],
'App Stats'[DeviceName]
),
MATCHBY('App Stats'[TimeStamp])
),
'App Stats'[AppName] = "AAD token broker plugin"
)
)Hi DoctorYSG ,
Z-Score with Partitioning in a Calculated Column:
Assuming your table is 'App Stats' and the relevant columns are belowWaitTimeMS (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
5 Replies
Hi DoctorYSG ,
Z-Score with Partitioning in a Calculated Column:
Assuming your table is 'App Stats' and the relevant columns are belowWaitTimeMS (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
Hi DoctorYSG ,
To create a calculated column with an aggregate window partition, try using the following DAX:zzTokenWait = VAR windowRows = WINDOW( 1, ABS, -1, ABS, 'App Stats', ORDERBY('App Stats'[TimeStamp]), DEFAULT, PARTITIONBY( 'App Stats'[AppName], 'App Stats'[AppVersion], 'App Stats'[BaseCode], 'App Stats'[DeviceName] ), MATCHBY('App Stats'[TimeStamp]) ) VAR filteredRows = FILTER(windowRows, 'App Stats'[AppName] = "AAD token broker plugin") VAR mean = AVERAGEX(filteredRows, 'App Stats'[WaitTimeMS]) VAR std = STDEVX.P(filteredRows, 'App Stats'[WaitTimeMS]) RETURN DIVIDE('App Stats'[WaitTimeMS] - mean, std, 0)
If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,Samson
This looked really good, and I tried it, but I get the error shown below. I even added more factors to the MatchBy (AppName, AppVersion, BaseCode, and DeviceName) but it still gives the match error below. The solution from maruthisp worked though. But I would still like to understand if your solution can be fixed, since it is usuable as a measure as well as a calculated column.
Hi DoctorYSG , Glad to hear the solution from maruthisp worked!
Here is a fix for my solution. As a note, please replace 'App Stats'[Id] with the unique identifier in your table:zzTokenWait = VAR windowRows = WINDOW( 1, ABS, -1, ABS, 'App Stats', ORDERBY('App Stats'[TimeStamp]), DEFAULT, PARTITIONBY( 'App Stats'[AppName], 'App Stats'[AppVersion], 'App Stats'[BaseCode], 'App Stats'[DeviceName] ), MATCHBY('App Stats'[TimeStamp], 'App Stats'[Id]) -- replace 'App Stats'[Id] with the unique identifier in your table ) VAR filteredRows = FILTER(windowRows, 'App Stats'[AppName] = "AAD token broker plugin") VAR mean = AVERAGEX(filteredRows, 'App Stats'[WaitTimeMS]) VAR std = STDEVX.P(filteredRows, 'App Stats'[WaitTimeMS]) RETURN DIVIDE('App Stats'[WaitTimeMS] - mean, std, 0)
Hi DoctorYSG
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
