topic Calculated Column with aggregate window partition in DAX Commands and Tips

Calculated Column with aggregate window partition

DoctorYSG — Thu, 29 May 2025 22:49:28 GMT

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)

I want something for mean that looks more like this measure. But how to do it? Just cut and paste from the measure results in circular reference error:

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" ) )

Re: Calculated Column with aggregate window partition

SamsonTruong — Thu, 29 May 2025 23:27:39 GMT

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

Re: Calculated Column with aggregate window partition

maruthisp — Fri, 30 May 2025 02:29:43 GMT

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

Re: Calculated Column with aggregate window partition

Anonymous — Mon, 02 Jun 2025 05:31:25 GMT

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.

Re: Calculated Column with aggregate window partition

DoctorYSG — Wed, 04 Jun 2025 15:22:47 GMT

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.

Re: Calculated Column with aggregate window partition

SamsonTruong — Wed, 04 Jun 2025 18:01:02 GMT

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)