3 Replies Latest reply on Apr 21, 2018 11:04 PM by Shinichiro Murakami

# Rolling Up Data Across Dimensions

I'm trying to take the sum of numbers from one dimension to use them in other dimensions in an allocation calculation.  I have the allocation calc's down but am having a challenge wrapping my head around the calcs I need to do the following:

I have three top level dimensions, PROJECT, SERVICE, and OTHER.  I have a set of values under OTHER that I want to sum up and drop into a calculated field.  That calculated field is used in an allocation calculation using LOD calcs.

Right now when I'm doing a traditional SUM(IF calc it's only pulling values in that dimensional structure.  I'm sure there's some type of LOD that I need to run I'm just not sure what.

Attached is a workbook that mocks up what I'm looking to do.  First I want to sum up all of the values for COST-A that are under the top level dimension of OTHER.  Then I want to take that summed up value and make it available as a calculated field so that if I dropped it in view it would be the total sum of all of COST-A (that way I can do a weighted average allocation later).

Thanks

• ###### 1. Re: Rolling Up Data Across Dimensions

HI James,

I hope I understand your request.

You need to use nested LOD in this case.

{fixed :min({exclude [TLD]:sum(if [TLD]="OTHER" then [Cost-A] end)})}

Thanks,

Shin

• ###### 2. Re: Rolling Up Data Across Dimensions

Hi Shin -

This is a great start, thank you for sending.  I noticed that in my example I failed to include an additional dimension of time that's captured in additional columns.  Each month is in its own column in the viz.  If I'm working with one month, this calc works well.  If I start working with multiple months, the number returned is the sum for all Cost-A across all months, not just the month in question.  How would I adjust the calc to accommodate an additional dimension of month?

Thanks!

James

• ###### 3. Re: Rolling Up Data Across Dimensions

HI James,

Previous one, I made it too much complicated and it was enough with

{fixed :sum(if [TLD]="OTHER" then [Cost-A] end)}