Wrong Total in a Straight Table

[malmsteen1977]

Hello all,

I searched the community but I didn't get a satisfying answer.

I have a simple straight table with 3 rows: 

- Amount previous year 

- Amount current year

- The third row should only sum those Parts (Dimension PartID), which have a value >0 in previous AND current year.

I have an if-Clause to do this, which works correctly on dimension-base, but the TOTAL is wrong (2.947.990,80). When you sum up the correct Part-Values, you only get 2.808.454,80

The field "AcceptedAmount" in the Expression is based on single DocumentNumbers (DocNo) and Positions (PosNo) in the table XI_PurchFacts and aggregated on Parts (PartID as Dimension)

I already tried some aggr(sum)-Expressions, but the Total is still wrong.

Sample data attached. Any advice is appreciated 🙂 

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(
IF(Sum({<MainDateYear={$(=only(MainDateYear)-1)}>} AcceptedAmount) <>0 	and Sum({<MainDateYear={$(=only(MainDateYear))}>} AcceptedAmount) <> 0, 
		Sum({<MainDateYear={$(=only(MainDateYear)-1)}>}AcceptedAmount),0)
, PartID))

[malmsteen1977]

Perfect Sunny. Thank you very much!!!

[sunny_talwar]

In fact, since this is a straight table... you can continue to use this

IF(Sum({<MainDateYear={$(=only(MainDateYear)-1)}>} AcceptedAmount) <>0 	and Sum({<MainDateYear={$(=only(MainDateYear))}>} AcceptedAmount) <> 0, 
		Sum({<MainDateYear={$(=only(MainDateYear)-1)}>}AcceptedAmount),0)

but change the total mode to Sum of Rows

[malmsteen1977]

That's right, but when I change the Layout to Pivot, then I need your right answer.