cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
austinsense Impactful Individual
Impactful Individual

Problem with STDEV

I was working with a client to calculate Value at Risk over a rolling window of time - this requires calculating the standard deviation over the period which I figured would work just great - here's what I wrote ...

 

// Standard Deviation Formula over a 10 Day Rolling Window (Out of the Box)
:=
CALCULATE (
    STDEV.P ( Data[Price Change $] ),
    FILTER (
        ALL ( Data ),
        Data[Date]
            > ( MAX ( 'Date'[Date] ) - 11 )
            && Data[Date] < MAX ( 'Date'[Date] )
    )
)

 

This formula returned the correct number for the first 10 days but then just returned zero.  I went back to my CFA knowledge and wrote the standard deviation formula from scratch ...

 

// Standard Deviation Formula over a 10 Day Rolling Window (From Scratch)
:= VAR averageprice = [AVG Price Change Last 10 Days] VAR days = [COUNT Days] RETURN SQRT ( DIVIDE ( SUMX ( FILTER (ALL ( Data ), Data[Date] > ( MAX ( 'Date'[Date] ) - 11 ) && Data[Date] < MAX ( 'Date'[Date] ) ), ( Data[Prices Change $] - averageprice ) ^ 2 ), days ) )

 

This works great - curious if anyone has had problems with the standard deviation functions or if anyone sees an error in the intial STDEV formula that I constructed.

 

Thanks,

Austin

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Problem with STDEV

I think at this point you go to https://support.powerbi.com and use the Contact Support link to submit a bug.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Re: Problem with STDEV

Curious what happens if you use STDEVX.P with your FILTER instead of wrapping your STDEV.P inside a CALCULATE.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

austinsense Impactful Individual
Impactful Individual

Re: Problem with STDEV

Same result, I tried it both ways

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
Super User IV
Super User IV

Re: Problem with STDEV

I think at this point you go to https://support.powerbi.com and use the Contact Support link to submit a bug.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

taumirza Helper IV
Helper IV

Re: Problem with STDEV

Hi @austinsense and @Greg_Deckler

I have the same problem

Fact (Itemkey,DateKey,Demand) Demand is Measure from source.

DimDate (DateKey,MonthNumber)

DimItem(ItemKey,ItemNumber,CompanyKey)

Now i am trying to create standard deviation but the function 'Stdev.P' is not working.

all values are as 0's

So i tried another way by calculating individual steps:

Note: All the calculations are Measures.

CounOfMonth = COUNT(DimDate[MonthNumber])

Mean = CALCULATE([CounOfMonth],ALLSELECTED(DimItem[ItemNumber]),ALLSELECTED('Fact'))

AvgDemand = DIVIDE(CALCULATE(SUM('Fact'[Demand]),ALLSELECTED('Fact'),ALLSELECTED(DimItem[ItemNumber])),[Mean],0)

Demand-AvgDemand = SUM('Fact'[Demand]) - [AvgDemand]

Sqr(Demand-Avg) = [Demand-AvgDemand]^2

Sqr(Demand-Avg)/Mean = DIVIDE([Sqr(Demand-Avg)],[Mean],0)

till here values are coming fine but not totals. and i concern about totals why because i once i remove MonthNumber from the table only one record and one total will which will be the same values.

OR Combining all above formulas into a single Measure is also coming fine, as:

StdDev = DIVIDE((CALCULATE(SUM('Fact'[Demand]),ALLSELECTED(DimItem[ItemNumber])) - DIVIDE ( SUMX (ALLSELECTED('Fact'),'Fact'[Demand]),[Mean]))^2,[Mean])

 

Capture.PNG

Once you add all values in Sqr(Demand-Avg)/Mean column

OR

Once you add all values in StdDev column

AND

Try try to do square root of it it is giving me the correct standard deviation as follows:

Capture.PNG

 

I filtered on a single item and working on it.

But when i  try to complete standard deviation formula by applying SQRT to StdDev:

StdDev = SQRT(DIVIDE((CALCULATE(SUM('Fact'[Demand]),ALLSELECTED(DimItem[ItemNumber])) - DIVIDE ( SUMX (ALLSELECTED('Fact'),'Fact'[Demand]),[Mean]))^2,[Mean]))

OR

by creating another Measure:

SQRT = SQRT(CALCULATE([Sqr(Demand-Avg)/Mean],ALLSELECTED(DimItem[ItemNumber]),ALLSELECTED('Fact')))

The results looks like this

 

Capture.PNG

Why my totals are not coming fine?

What did i miss?

 

Thanks in advance.

 

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors