I have a problem in Pivot Table which has the following columns:
The Columns 1,2,4,5 are made using a simple Sum() expression. Column 3,6 have the Following calculation:
Column 3= Column2/Colum 1
Column 6= Column 5/Column4
Column 7 = (Column3-Column6) * Column1
Column 8 = (Column2-Column4)* Column 3
In the Pivot table, when I enable the partial totals, the Column1, 2, 3,4 gives a total of all the rows above thne Partial Sum. But for the Column 3, the partial sum value given is based on the expression "Column2/Colum 1". I want it instead to be the sum of row values above the Partial sum. The same thing applies for Col 6, 7 & 8.
I have used the Sum(aggr()) for the expressions in the pivot table, but then when I expand the pivot table, I have ITEM_NO as the last dimension in the pivot table, which can repeat. The Sum(Aggr()), aggregates the value of all the similar ITEM_NO and displays the value against one of the ITEM_NO and shows 0 against the rest of them. You can see it in the attached qvw with this post.
I would appreciate any suggestions or help in this.