topic Re: StDev discrepancy when using similar tables in DAX Commands and Tips

StDev discrepancy when using similar tables

TMO_KY — Tue, 19 Sep 2023 19:01:25 GMT

I wasn't exactly sure how to word my problem to make it stand out from similar but different issues. Anyway, I have a DAX table that I created and it works flawlessly but I was recently asked if I could add an additional column, a unique ID, to the table but when I did, it throws my StDev off, therefore throwing off my Z-Score and I'm not sure how to fix it.
Below is the Expected vs Incorrect output columns.

The measure I'm using for the Expected Output visual is as follows:

StDev_24_TEST = CALCULATE(STDEV.P('24MonthTable'[Failures]), DATESINPERIOD('24MonthTable'[Month-Year], LASTDATE('24MonthTable'[Month-Year])-1, -24, Month ) )

I tried using the same measure for my "duplicate" table but it returned all 0's. I tried several different methods and all but one variation returned 0.0. The measure that "somewhat" worked, and I use that term very loosely was:

STD24 = VAR s = CALCULATE( STDEV.P('TEST'[Failures]), ALL('TEST'), -- Remove any other filters on 'TEST' DATESINPERIOD( 'TEST'[Month-Year], LASTDATE('TEST'[Month-Year]) - 1, -24, MONTH ) ) RETURN s

The ONLY difference between the tables is one column, [PR ID] which I was planning on using to link to other tables because it's a unique identifier.

Re: StDev discrepancy when using similar tables

lbendlin — Thu, 21 Sep 2023 01:49:20 GMT

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: StDev discrepancy when using similar tables

TMO_KY — Thu, 21 Sep 2023 12:36:53 GMT

Ive uploaded a pbix file with sample data to my dropbox. Hope this helps.

StDev_SampleData.pbix

Re: StDev discrepancy when using similar tables

lbendlin — Thu, 21 Sep 2023 21:46:08 GMT

In your TEST table each failure has its own row, therefore the standard deviation is computed across all these 1 values. You need to use STDEVX.P rather than STDEV.P

Having said that I can only get half of it to work.

Re: StDev discrepancy when using similar tables

TMO_KY — Fri, 22 Sep 2023 12:19:16 GMT

That's more than where I was able to get so I appreciate it. I'll have to talk to my end user and get their thoughts. if the Product Family will be in a visual or not. I know it's used as a filter sometimes but not always.

What if there was a sum measure for the Failures in the test table and do the stdevx against a measure?

Re: StDev discrepancy when using similar tables

lbendlin — Fri, 22 Sep 2023 12:29:08 GMT

I think that should work, as long as that measure evaluates for each row of the table argument in STDEVX.P . The trick here is to present a suitable table - which can also be a table variable.

Re: StDev discrepancy when using similar tables

TMO_KY — Fri, 22 Sep 2023 12:33:20 GMT

I appreciate the insight.....and I need to see what's going on, your suggestion worked for the data I provided (hard-coded in query) but once put in with live data, it throws different values, even when filtered, and all data types are the same. Either way, I appreciate the help and quick responses.