cancel
Showing results for
Did you mean:
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 🙂
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

Thank you for the kudos 🙂

Proud to be a Datanaut!

Announcements

#### 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?

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

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