Using subtraction with a running total

[mellerbeck]

So I have a pivot table

Quantity Limit        PO #     Received Qty Totals     Balance Remaining

333,492                    1          46,669                        286,823

                               2          126,673                       160,150

                               3          46,669                         113,981

And I'm try to calculte the Balance Remaining Which is basically the running total of Quantity Limit - Received Qty Totals

I've tried

RangeSum(-[Received Qty Totals], Above([Balance Remaining]))

But so far no dice, any help would be much appreciated.

Thanks,

Michael

[spividori · Accepted solution]

Hi.

See the example.

Hope this help!

Regards.

[mellerbeck]

Hi Sandro, this is exactly what I want! Only problem now is that the total line (using Show Partial Sums) looks like it is a null now (or doesn't work). Reading from

(John Witherspoon)

A pivot table, and also straight tables by default, reevaluate your expression for the total row instead of doing a sum of rows. A straight table lets you override this default behavior directly and tell it you want a sum of rows. A pivot table doesn't let you do that. So you have to build your "sum of rows" manually using sum(aggr()). Like this, putting in your dimensions instead of what I listed:

sum(aggr(RangeMax(0, sum((FORMPERCENTAGE * Quantity)/100)),YourFirstDimension,YourSecondDimension,etc.))

I can sort of grasp the concept that I need to Total it manually, but can't quite figure it out. Any thoughts?

Thanks!    

[johnw]

It's not null for me.  If I tell it to give me a partial sum for POs, I get 220011 and 113481, which make sense to me.

[spividori]

Hi.

I had used a few instances in which John mentioned. You could post an example to try to solve it?

Regards.

[mellerbeck]

Here is what it looks like, it probably is something obvious.

Message was edited by: mellerbeck

[johnw]

Here's how I interpret Sandro's solution.  Difference in red.  The partial sums line then has a total.

if(IsNull(Above([Balance Remaining])),[Quantity Limit]-[Received Qty Totals],above([Balance Remaining])-[Received Qty Totals])

[mellerbeck]

Thank you!

[johnw]

Can you give Sandro the credit for the solution?  All I did was clarify how to interpret his solution for your specific field and column names.

[mellerbeck]

Oh, right. Done