Subtotal with only visible values

[#1]

Hey everyone, good morning!

I have a pivot table that calculates volume deviations between two periods.

Ex: (Production of a 'x product')

Day	Morning (lb)	Afternoon (lb)	Desv.
18/05	1000	500	-500
19/05	200	300	100
20/05	400	5000	4600

Where 'Day' is a dimension and Morning, Afternoon and desv are expressions.

All these expressions are conditionated to this condition: If(sum of morning) - sum(afternoon) is > 400 or <-400, expression (sum or m/a), else, 0.

After that I hide 0, so my table gets this way:

Day	Morning (lb)	Afternoon (lb)	Desv.
18/05	1000	500	-500
20/5	400	5000	4600

However, when I insert the subtotal into the table, it remains calculating the whole table:

Day	Morning (lb)	Afternoon (lb)	Desv.
18/05	1000	500	-500
20/05	400	5000	4600
Total			4200

Do you guys know how to sum only the visibles values?

Kind regards, Luiz Bisco

[stabben23]

Add a sum first in Your Expression. Do not start With an if statment:

[richnorris]

You could change your Desv calculation, to be the one you said,

If(sum of morning) - sum(afternoon) is > 400 or <-400, expression (sum or m/a), else, 0

so that your top chart (prior to conditional show) would have a 0 in the Desv column. This way your subtotal would be what you expect?

/Edit: Also, yes as Staffan says, its not good practice to start with the if, you should start with 'SUM' in the epression, so something like:

SUM( if( (sumOfMorning)-(sumOfAfternoon) >400

         or (sumOfMorning)-(sumOfAfternoon) <-400,

         (sumOfMorning - sumOfAfternoon),

          0)

         )

[marcus_sommer]

You need to calculate this on the day-level with an aggr-function, like:

sum(aggr(YourExpression, Day))

- Marcus

[tamilarasu · Accepted solution]

Hi Luiz,

Yes. You need to use Aggr in order to get total value of visible rows. Like below

Sum(Aggr(If(sum ([Morning (lb)]) - sum([Afternoon (lb)])>400 or sum ([Morning (lb)]) - sum([Afternoon (lb)])< -400,Sum([Morning (lb)]),0),Day))

[#6]

But if I do that, how can show only the desviations that I want (only > 400 or <-400)?

[#7]

Tamil, thank you!

It works, but now I have another problem. The total is correct for the summing, but i tried to insert a column with percent (and the conditions to calc remains). see the example:

Day	Morning (lb)	Afternoon (lb)	Desv.	Desv %
18/05	1000	500	-500	-50%
20/05	400	5000	4600	1150%
Total	1400	5500	4100	1100%

The desv %, should be 292,8% (4100/1400), but, its is giving me the sum of Desv %.

Do you know how to handle with it?

I'm already greatful,

Sincerely, Luiz Bisco

[tamilarasu]

Hi Luiz,

I have attached a sample app. You can simply refer the column label like below,

[Desv.] / [Morning (lb)]