Getting Standard Deviation on counts if ID rolled off at month level

[Sam01]

Hello Power BI team,

I am trying to get Standard deviation based on monthly count but running into issues. I was not able to use biult in function STDEV but did not work wondering if anyone was able to calculate Standard deviation on scenario shown an in attached .pbix? 

https://1drv.ms/u/s!Ak_CUb7-a3wXakVfzU08cCNSHBE

TIA

[sajidtp]

Hi Sam, this worked for my scenario recently using Matt Ellington's solution: 

Monthly Standard Deviation with Calendar = CALCULATE(

STDEVX.P('DimDate1',

[Monthly Avg]),

ALL('DimDate1'[Month])

)

Check othis out: https://exceleratorbi.com.au/six-sigma-control-charts-in-power-bi/

[Sam01]

@sajidtp  Hi thanks for your relpy, following measure is returning  0.41 as standard deviation but in actual it should be 5.9, any thoughts?

[sajidtp]

Hi @Sam01 negative, newbie to Std. Dev. as well... still learning...

[Anonymous · Accepted solution]

@Sam01 

What about something like this:

Standard Deviation Attempt = 
    STDEVX.P(
        ADDCOLUMNS( 
            SUMMARIZE( 
                'Fact', 
                DimDate1[Month],
                DimName[Name]
            ), 
        "Count", 
        [ActualCount]
        ),
        [ActualCount]
    )

[sajidtp]

Thank you @Anonymous this is awesome! 

[Sam01]

@Anonymous  Perfect, thank you so much.