Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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!
Hi,
Kindly post your application. That will give better view of your requirement.
Regards,
Kaushik Solanki
Here's the application.
Thank you!
Hi,
Can you please tell me, with this selection what output you are expecting.
Regards,
Kaushik Solanki
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)
Any idea?
Thank you!!