cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
charu1313 Regular Visitor
Regular Visitor

standard deviation per category per year

Hello,

 

This is in continuation to previous question about calculating average per category per year. That went well. 

But now i'm stuck at calculating standard deviation per category per year. 

 

so here is the detail of the data. 

I have data where i need to calculate the standard deviation of units per category (apo key+ material key make a unique category for my data) per year (which in my case is observed as previous year (PY), current year (CY) and new year (NY) and is available in the column 'is year')

 

now i have calculated the avergae using the formula- 

Average =
CALCULATE (AVERAGE (tab[units] ),
FILTER ('tab', 'tab'[Material - Key]=EARLIER( 'tab'[Material - Key]) && 'tab'[ISYEAR]=EARLIER('tab'[ISYEAR]) && 'tab'[APO Key]= EARLIER('tab'[APO Key]))
)
 
and now need to calculate standard deviation. The STDEV.X uses the formula sqrt(sum(X-Xavg)^2/n) but my n has to change per cateory per year and so i cannot apply this straightaway. 
 
Is using STDEVX.P a better approach? if so, then what would be the expression that i must supply to the formula.
 
Or should i break down the formula into columns and the calculate 'n' seperately? in that case, how should i calculate 'n'?
 
Essentially the column in the green is what i want to obtain. 
 
Thank you 🙂
sd sample.PNG
1 REPLY 1
Super User II
Super User II

Re: standard deviation per category per year

Hi, can you paste that data in the copiable table format as below?

Column1 Column2
A 1
B 2.5

Then I could work on the formula in PowerBI

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors