Skip to main content
Announcements
Discover what’s possible with embedded analytics! March 6, 10 AM ET SIGN UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
elijahabel
Contributor III
Contributor III

Std Dev in a pivot table

I have a problem where I need to be able to display Standard Deviation in a pivot table. The biggest issue is that it needs to be calculating the standard deviation of the Daily net regardless of the first dimension in the pivot rows.

For example, given the below data:

DateDivisionDepartmentDaily Profit
6/3/2018AA150
6/4/2018AA160
6/5/2018AA110
6/4/2018AA2-30
6/5/2018AA230

In our pivot table, with Divisionas the first dimension in the "Rows" section, it should be taking stdev(50, 30, 40) because we only care about he standard deviation of the daily total values (daily totals shown below).

DateDivisionDaily TOTAL
6/3/2018A50
6/4/2018A30
6/5/2018A40

With Department as the first dimension in the "Rows" section of the pivot table, it should take stdev(50, 60, 10) for division A1, and stdev(-30, 30) for A2, since these are the daily totals for those divisions.

Any help on which formula I could use to calculate this? Thank you in advance for all help.

If I use the following, then  for Division it will take the stdev(50, 60, 10, -30, 30):

stdev(aggr(Sum(Daily Profit),Division,Department,Date))

9 Replies
sunny_talwar

Try this

Stdev(Aggr(Sum([Daily Profit]), Date))

elijahabel
Contributor III
Contributor III
Author

When I try this, many of the Standard Deviation values for Divisions show up as "-". Not sure why this is, but when I filter for a single division, its value will change from "-" to a number even if no other filters are selected. I do not understand why the Standard deviation would change when none of the Daily Profit values under the Division have after filtering.

sunny_talwar

Try this

Aggr(NODISTINCT Stdev(Aggr(Sum([Daily Profit]), Date)), Date)

sunny_talwar

Or may be this

Stdev(TOTAL Aggr(Sum([Daily Profit]), Date))

elijahabel
Contributor III
Contributor III
Author

No luck with these either.

Aggr(NODISTINCT Stdev(Aggr(Sum([Daily Profit]), Date)), Date) results in "-" no matter the drilldown level.

Stdev(TOTAL Aggr(Sum([Daily Profit]), Date)) results in all divisions having the same Stdev  value, whereas each should have their own independent value.

sunny_talwar

Would you be able to share a sample to check this out?

ogautier62
Specialist II
Specialist II

Hi,

the only way I see is to add Date as dimension :

so use stdev(Daily Profit) as formula !

and use dimensionality() to display stdev and not row daily details

regards

kaanerisen
Creator III
Creator III

Hi Elijah,

Is this what you are looking for?

Expression:

IF(GetObjectDimension(Dimensionality()-1)='Division',Stdev(aggr(sum([Daily Profit]),Date)),Stdev([Daily Profit]))


Untitled.png

Hope it helps.

elijahabel
Contributor III
Contributor III
Author

This works very well for this case, so thank you! Do you know how this solution could be expanded for more than 2 dimensions? Let's say there was a third dimension named "Store" - would there be a way to do this intricately without nested IF statements?

We actually use more than 2 dimensions in the case I'm looking for help on, I just can't post proprietary stuff on here, so I used this example.