Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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.

 

 

 

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Same result, I tried it both ways

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

@austinsense What is the calc. behind the VAR (averageprice and days) below:

// 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 ) ) 

 I am running into issue as well when using the STDDEVX.S formula . 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors