Forum Discussion

TMO_KY's avatar
TMO_KY
Helper II
2 years ago

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