cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sam01 Frequent Visitor
Frequent Visitor

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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Nick_M New Contributor
New Contributor

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

@Sam01 

What about something like this:

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

 

Std Dev.png

 

6 REPLIES 6
Highlighted
sajidtp Frequent Visitor
Frequent Visitor

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

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])
Sam01 Frequent Visitor
Frequent Visitor

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

@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 Frequent Visitor
Frequent Visitor

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

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

Nick_M New Contributor
New Contributor

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

@Sam01 

What about something like this:

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

 

Std Dev.png

 

Sam01 Frequent Visitor
Frequent Visitor

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

@Nick_M  Perfect, thank you so much.

sajidtp Frequent Visitor
Frequent Visitor

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

Thank you @Nick_M this is awesome!