Using the then VALUE in a Cross Table

[Grant Mitchell]

Hi - I am hoping somebody can help please.

I am trying to replicate this setup from Excel:

 

I have been able to create everything within a Cross Table except for CALC 3 - and I'm not sure if it's to do with my formula or something else.

This is what I have for creating CALC 1 and CALC 2 (excuse the need for Case Statements - the underlying data table needs to remain in the structure it is in).

 

Sum(case

when [Table]="Row 1" then [# Qty]

end)

+

Sum(case

when [Table]="Row 2" then [# Qty]

end)

-

Sum(case

when [Table]="Row 3" then [# Qty]

end) as [CALC 1],

 

Sum(case

when [Table]="Row 1" then [# Qty]

end)

+

Sum(case

when [Table]="Row 2" then [# Qty]

end)

-

Sum(case

when [Table]="Row 3" then [# Qty]

end) then sum([Value]) over (AllPrevious([Axis.Columns])) as [CALC 2]

 

I try to create CALC 3 as the following - but the formula errors out

 

Sum(case

when [Table]="Row 1" then [# Qty]

end)

+

Sum(case

when [Table]="Row 2" then [# Qty]

end)

-

Sum(case

when [Table]="Row 3" then [# Qty]

end) then sum([Value]) over (AllPrevious([Axis.Columns]))

-

Sum(case

when [Table]="Row 3" then [# Qty]

end) as [CALC 3]

 

Is anybody able to help calculate CALC 3 - within the Cross Table I think it's related to using a calculated cumulative value alongside a non-cumulative value.

 

Thanks,

Grant