Grant Mitchell Posted March 12, 2021 Share Posted March 12, 2021 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 Link to comment Share on other sites More sharing options...

## Recommended Posts

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account## Sign in

Already have an account? Sign in here.

Sign In Now