Row-based calculation where cell value is 0

[#1]

Hello community,

my goal is to not include calculations for 0 or null values in the Planned column so that they won't be added in the (Act - Plan) column.

This needs to be row-based.

I cannot do this in the load script because the data comes from different tables.

Material	Actual	Planned	(Act - Plan)
Total	10	4	4
A	8	4	4
B	2	0	0

So the question is how to do this row-based calculation?

Thank you for your Input.

[m_woolf]

=if(Plan>0,Act-Plan,0)

[vinieme12]

like below !

=if(Planned>0,Actual-Planned,0)

[#4]

Ok, I do understand my own problem better now 🙂

In my calculation I use sum functions for all three calculations. When I use above formulas I get the right calculation for each row but the total field is 0.

I tried different modifications of the formula but all resulted in a total value of 0.

For example:

=if(Planned>0,sum(Actual)- sum(Planned),0)

Is tere a way to fix this?

[avinashelite]

Go to expression > Total > select the Row>total option that should help you to get the Total

[vinieme12]

please post a sample app

Preparing examples for Upload - Reduction and Data Scrambling

[#7]

You are right, it works for a straight table to set the Total Mode to "Sum".

Is there a way to do this for a Pivot table as well?

[ahaahaaha]

Hi,

May be as in attached file

Regards,

Andrey

[avinashelite]

Did you tried with the partial sum option in the presentation tab ?