Calculated Column with aggregate window partition

[DoctorYSG]

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

[v-priyankata]

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.

[maruthisp · Accepted solution]

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

[SamsonTruong]

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

[DoctorYSG]

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.

[SamsonTruong]

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)