Forum Discussion

DoctorYSG's avatar
DoctorYSG
Helper III
1 year ago
Solved

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 wan...
  • maruthisp's avatar
    maruthisp
    1 year ago

    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

DoctorYSG's avatar
DoctorYSG
Helper III
1 year ago

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's avatar
SamsonTruong
Super User
1 year ago

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)