2 Replies Latest reply: Sep 15, 2011 2:13 PM by Marc Livingston

# Set analysis/Running total from the top dimension to lower dimensions pivot table

I need to Pass a total from a pivot table dimension to the lower dimensions to use in a formula.

Current Dimensions:

Sales

Cost of Goods Sold

Selling

I want the Sales minus the totals from each column.

Sales                                   155

Cost of Goods Sold             200         155-200

Selling                                  168         155-168

I tried this, but it is not working, just shows 0 for the Sales so I get - values of the first column

```=sum({<Topincome={"Sales"}>} if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt)) - sum(if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3,creditamt+debitamt))
```

I was able to do this is Crystal Reports using a running total that is summarizing only the sales, and then passing it down. How could I do this here?

Image for visualization:

• ###### Set analysis/Running total from the top dimension to lower dimensions pivot table

Ok I somewhat got this to work using a 'Ready to Calculate' button that sets variables when all the selections are made. Would prefer if this was automated, but it is working for now.

Anyone have any other ideas?

• ###### Re: Set analysis/Running total from the top dimension to lower dimensions pivot table

So my supervisor does not like the solution of having to click a button to get this to work.

Is there any way to set a variable based on if the value changes?

Example:

Dimension  balance

Sales          52,000

COGS        25,000

Selling        10,000

I need to calculate Margin, Only for COGS. For this formula, I need the sum(balance) for only Sales.

This Formula is not working:

```=if(Topincome='Cost of Goods Sold',

(sum({<Topincome={"Sales"}>} if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))*-1)

-(Sum(if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))))
```

However this formula:

```=if(Topincome='Cost of Goods Sold',\$(Sales)-(Sum(if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))))
```

with the variable set to:

```=sum({<Topincome={"Sales"}>} if(left(text(segvalue1),1)<>1 and left(text(segvalue1),1)<>2 and left(text(segvalue1),1)<>3, creditamt+debitamt))*-1

```

Works perfectly.

Can anyone help me out?