- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Problem with STDEV

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

austinsense

Established Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-19-2016
07:06 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Greg_Deckler

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2016
09:15 PM

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

Proud to be a Datanaut!

4 REPLIES 4

Greg_Deckler

Super User

Re: Problem with STDEV

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-19-2016
10:25 AM

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

Proud to be a Datanaut!

austinsense

Established Member

Re: Problem with STDEV

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2016
08:31 PM

Same result, I tried it both ways

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

Greg_Deckler

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2016
09:15 PM

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

Proud to be a Datanaut!

taumirza

Member

Re: Problem with STDEV

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-27-2016
07:59 AM

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.