Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi.
See the example.
Hope this help!
Regards.
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!
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.
Hi.
I had used a few instances in which John mentioned. You could post an example to try to solve it?
Regards.
Here is what it looks like, it probably is something obvious.
Message was edited by: mellerbeck
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])
Thank you!
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.
Oh, right. Done