March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
I think at this point you go to https://support.powerbi.com and use the Contact Support link to submit a bug.
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])
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:
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
Why my totals are not coming fine?
What did i miss?
Thanks in advance.
Curious what happens if you use STDEVX.P with your FILTER instead of wrapping your STDEV.P inside a CALCULATE.
Same result, I tried it both ways
@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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
134 | |
91 | |
89 | |
64 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
72 | |
68 |