7 Replies Latest reply: Jul 25, 2013 1:49 PM by ILARIA CREMONESI

# Custom total of rows in pivot table

Hello everyone,

I am trying to build a pivot table with a custom total of rows, i.e. not the total of all the rows but with the total of some of the rows of the table based on the value of an expression.

See the image attached:

I want to get a row with the sum of the rows where the value computed is not null

For example

the total of  column Variante = 24 is 176.523

the total of  column Variante = 23 is 177.919

the total of  column Variante = 22 is 181.183

What I would like:

the total of  column Variante = 24 to be 175.952 ( i.e. the total of 176.523 minus 570 because the item M10414N90 is not shared by Variante 24, 23 and 22)

the total of  column Variante = 23 to be 177.919

the total of  column Variante = 22 to be 181.183

Thank you!

• ###### Re: Custom total of rows in pivot table

You could exclude this item per using if-loops or set analysis in the expression:

if(Articolo <> M10414N90, sum(VALUE))

Are there many excludes it is easier to generate appropriate flags in load script.

- Marcus

• ###### Re: Custom total of rows in pivot table

Actually the exclusion should be based on the value of the expression, i.e. if for at least one row the value is zero then the total should be computed by excluding this row

For example for "Articolo" M10414N90 the value is zero for "Variante" 23 and 22, thus the total should exclude this "Articolo". Is there any expression or custom dimension I could build?

Thank you!

• ###### Re: Custom total of rows in pivot table

Hi,

Kindly post your application. That will give better view of your requirement.

Regards,

Kaushik Solanki

• ###### Re: Custom total of rows in pivot table

Here's the application.

Thank you!

• ###### Re: Custom total of rows in pivot table

Hi,

Can you please tell me, with this selection what output you are expecting.

Regards,

Kaushik Solanki

• ###### Re: Custom total of rows in pivot table

I would expect:

column Variante 24 a total of 506

column Variante 23 a total of 506

column Variante 22 a total of 521  (i.e. 27 + 494 because the second row presents null values for the other two colums. It is sufficient that one column presents null value for the row to exclude it from the total)

• ###### Re: Custom total of rows in pivot table

Any idea?

Thank you!!