Calculating Standard Deviation for a Z-Score

Hi Frasermc98 ,

The issue you're encountering likely arises from how filters are managed in your DAX formula. To ensure that the Z-Score calculation ignores any filters applied to [Session Date], the ALL function needs to be used properly to clear the filter context. You can modify your standard deviation formula as follows:

STDEVX.P(
    SUMMARIZE(
        ALL('MDGPS - MIP'[Session Date]), 
        'MDGPS - MIP'[Session Date]
    ),
    CALCULATE(SUM('MDGPS - MIP'[Extensive Peak Efforts]))
)

This formula ensures that filters on [Session Date] are removed by using ALL('MDGPS - MIP'[Session Date]) within the SUMMARIZE function, creating a table of unique session dates that is unaffected by slicers. The CALCULATE function ensures the sum of [Extensive Peak Efforts] is evaluated in this filter-free context.

For the Z-Score calculation, you can construct the following formula:

Z-Score = 
VAR MeanEffort = AVERAGEX(
    ALL('MDGPS - MIP'[Session Date]), 
    CALCULATE(SUM('MDGPS - MIP'[Extensive Peak Efforts]))
)
VAR StdDevEffort = 
    STDEVX.P(
        SUMMARIZE(
            ALL('MDGPS - MIP'[Session Date]), 
            'MDGPS - MIP'[Session Date]
        ),
        CALCULATE(SUM('MDGPS - MIP'[Extensive Peak Efforts]))
    )
RETURN
DIVIDE(
    CALCULATE(SUM('MDGPS - MIP'[Extensive Peak Efforts])),
    StdDevEffort
) - MeanEffort

This formula calculates the mean and standard deviation of the extensive peak efforts across all session dates without being affected by dashboard filters. The MeanEffort variable computes the average effort by clearing the [Session Date] filter context, while StdDevEffort calculates the standard deviation in the same way. The Z-Score is then computed by dividing the sum of [Extensive Peak Efforts] by the standard deviation and subtracting the mean.

To troubleshoot, validate that your intermediate measures for mean and standard deviation are returning the correct values. Ensure that all necessary dates and efforts are included in your dataset and that no unintended filters or relationships are interfering with the calculation. If these steps are followed, your Z-Score calculation should work as intended, even when filters are applied on the dashboard.

Best regards,