Forum Discussion
StDev discrepancy when using similar tables
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.
6 Replies
- lbendlinSuper User
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- TMO_KYHelper II
Ive uploaded a pbix file with sample data to my dropbox. Hope this helps.
StDev_SampleData.pbix- lbendlinSuper User
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.