Z-Scores the LoD Edition!

Version 3

    So I noticed that there wasn't an example workbook yet on how to calculate Z-Score using LoDs.

     

    In this example I copied the method used in

     

    Calculating Z-scores | Tableau Software

     

    So that comparisons can be made (Granted, I am using a different version of SuperStore).

     

    In the example above, Z-Score is calculated as follows

     

    ( SUM([Sales]) - WINDOW_AVG(SUM([Sales])) )

    /

    WINDOW_STDEVP(SUM([Sales]))

     

    The LoD Alternative is

     

    ({ FIXED [State] : SUM([Sales]) } - { FIXED : AVG({ FIXED [State]: SUM([Sales]) }) })

    /

    { FIXED : STDEVP({FIXED [State] : SUM([Sales])}) }

     

    Both, calcs result in the same output.

     

    11-17-2015 8-15-03 AM.png

     

    The biggest benefit you get from using an LoD alternative is the ability to control the grain at which the Z-Score is calculated and to which level of detail you wish to return it. This gives us much more flexibility/control over outliers in our datasets.

     

    I used FIXED in my example for ease of use, but INCLUDE/EXCLUDE LoDs could be used as well. However INCLUDE/EXCLUDE LoDs must be aggregated, which could result in less flexibility with the data.

     

    Any suggestions on how to improve this workbook is highly appreciated.

     

    And, of course, if I have done anything incorrectly here, please let me know!

     

    Regards,

    Rody