Need an Expression for Full Accumulation/Cumulative in a Pivot table

[#1]

I have a Table with a Cost Type Column containing: Obligated, Actual's and Projected across a Month Dimension ranging from Jan-2012 into Feb-2013 a rolling calendar so YTD wont be useful. I just need a 'simple' Cumulative running total based off my set analysis sample below.

I need get have a Cumulative summation from Cost type Obligated & Actual.  See Image below for ref.

The problem is Full accumulation only works in a Chart not a table. I'm new at this so I need a good starting point.

My Measure is : Amount

The Expression I use to display the Obligated Columns is: Sum({$<[Cost Type]={"Obligated*"}>}Amount)

This works fine. But if I want to compare my Actuals against my Obligated Funds which may not be in that Month then its shows I ran out of Funds even though they were allocated in Months past. The end goal is to color code the areas were the Cumulative is < less than the Cumulative Actual's for that month then flag it red.

Cumulative Obligated < Cumulative Actual's Then make Color background Red.

Thank you for any assistance rendered.

Brian

[#2]

That Did the trick!  Thanks!

If( Count({1<[Cost Type]={"Actual*"}>}Amount) > 0 , $(Cum_Actual) , 0 )

[whiteline]

're welcome

[#4]

Hi Whiteline,

I really need some help recreating the values in the blue dotted line into the Pivot table shown in my attachment. I was only able to replicate the accumulated sum for the next month projections after the actual's run out but not on the subsequent ones. 451K on the lien is correct the smaller value in the table is too low marked in red.

I was thinking of using 'After' to calculate the sums going forward?

Here is what I propose to match the dotted portion.

If ( Sum({1<[Cost Type]={"Actual*"}>}Amount) > 0 , $(Cum_Actual) , $(Cum_Actual)+Sum({1<[Cost Type]={"Projected*"}>}Amount) )

The +Sum({1<[Cost Type]={"Actual*","Projected*"}>}Amount)  part only works for the Month of Oct. but does not accumulate obviously going forward. 

This one wont work because its adds Projected too early. I need Projected to only accumulate once Actual's are   0

RangeSum(Before(Sum({1<[Cost Type]={"Projected*","Actual*"}>}Amount),0, ColumnNo()))

This is what I have in my Chart line Exp for reference. This gets accumulated by the chart accumulation So I naturally I thought the one above would work.

If(Sum({$<[Cost Type]={"Actual*"}>}Amount) > 0, Sum({$<[Cost Type]={"Actual*"}>}Amount) , Sum({$<[Cost Type]={"Actual*","Projected*"}>}Amount

I tried this but got weird results...

If ( Sum({1<[Cost Type]={"Actual*"}>}Amount) > 0 , $(Cum_Actual) , $(Cum_Actual) + RangeSum(After(Sum({1<[Cost Type]={"Projected*"}>}Amount),0, ColumnNo()))

[whiteline]

Hi, Brian.

Sorry, I think I didn't understand your issue.

But talking about chart you've mentioned ColumnNo() function. I think that's may be a problem.

It seems that 'behind' a chart QV organizes data as a vertically oriented table.

So I think, for manual accumulation you should use RowNo() instead:

=RangeSum(Before(Sum({1<[Cost Type]={"Projected*","Actual*"}>}Amount),0, RowNo()))

[#6]

Yeah my reply is confusing.  Essentaily the numbers I marked in red in the screenshot are so suppose to match up.

The logic is basically this; Actuals are calculated from a left starting point to some place in to the right. The projected will gernarally be present plsu in front of the actuals or 'After' hence why I thought the After clause would do it.

I will try the RowNo() and see what happens.

Thanks!