Announcements
Discover what’s possible with embedded analytics! March 6, 10 AM ET SIGN UP!
cancel
Showing results for
Did you mean:
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
MVP

Try this

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

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.

MVP

Try this

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

MVP

Or may be this

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

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.

MVP

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

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

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]))

Hope it helps.

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.

Tags
Community Browser