topic Calculating Standard Deviation for a Z-Score in DAX Commands and Tips

Calculating Standard Deviation for a Z-Score

Frasermc98 — Wed, 18 Dec 2024 11:44:16 GMT

Hi,

I have the following calculation which calculates the standard deviation for the Sum of Extensive Peak Efforts for a day.

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

This DAX works fine but now when trying to apply this into a Z-Score calculation whereby it needs to ignore any date filters appied on my dashboard it does not work and shows 0. I have tried the following but unsure what else to try:

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

Any help would be greatly appreciated.

Thanks.

Re: Calculating Standard Deviation for a Z-Score

DataNinja777 — Wed, 18 Dec 2024 14:21:41 GMT

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,

Re: Calculating Standard Deviation for a Z-Score

Anonymous — Thu, 19 Dec 2024 10:01:34 GMT

Thanks for the reply from DataNinja777 , please allow me to provide another insight:

Hi, Frasermc98
Thanks for reaching out to the Microsoft fabric community forum.

Could you please let us know if DataNinja777's response resolved your issue? If it did, kindly accept it as the solution.

Since the first parameter of the STDEVX.P() function and the second parameter of the CALCULATE() function both require a table data type, it is necessary that the table you are using is already unfiltered. This is why the ALL() function is nested within SUMMARIZE().

Of course, the ALLEXCEPT() function and REMOVEFILTERS are also functions that remove filters. Below is a screenshot of the relevant documentation:

For more details, please refer to:

ALL function (DAX) - DAX | Microsoft Learn
ALLEXCEPT function (DAX) - DAX | Microsoft Learn
REMOVEFILTERS function (DAX) - DAX | Microsoft Learn

If the issue remains unresolved, we would appreciate it if you could provide sample data and the expected results so that we can better assist you in resolving the problem.

.We recommend uploading it to GitHub and sharing the link with us.When uploading a file, please be careful to delete sensitive information.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.

Best Regards,

Leroy Lu

Re: Calculating Standard Deviation for a Z-Score

Kedar_Pande — Mon, 23 Dec 2024 12:22:10 GMT

Frasermc98

You need to use ALL('MDGPS - MIP') to ignore all filters on the table.

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

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn