Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm at a bit of a loss here!
I have created a straight table, with various formulas and fields, and its adding up perfectly.
However, when I change the type to a pivot table, to group, it changes all the 'Grand' totals to a totally different figure, even though the lines within the table are showing the correct values. I've exported the pivot to excel, and added it up that way, so I know the 'Grand Total' is wrong.
Any thoughts on the problem?
Have attached JPG image to show problem
Thanks
Di
Hi,
I don;t think people are understanding what I'm trying to say.
The problem I'm having is with the PIVOT table, NOT the straight table.
The pivot table is NOT adding up the calculated rows correctly like the straight table is. I won;t be using and other charts or tables, apart from what will be the one pivot table.
What I'm trying to understand is why the straight table will add the rows up correctly, based on the 'Sum of Rows' option, but, the PIVOT table, which is what I need to use, is NOT adding the calculated rows up correctly, even though in the 'Total Mode', the 'Sum of Rows' is selected.
Unfortunately, due to the company I work for, I am not allowed to give data out. I'm not sure though, even if I could gave this info out how it would change things? My data is right, my calculations are right, why are the totals in the two tables different?
If I flip the pivot table back to a straight table, it adds the calculated rows up correctly!
Di
Hi Diane,
Use AGGR in expression to Calculate correct grand Total,
For ex. I have 2 Dimensions in pivot table DIM1 and DIM2 and 1 measure Qty.
=AGGR(Sum(Qty),DIM1,DIM2)
Note: Include all dimension in AGGR function those are used in pivot table.
Regards,
Kiran
8976977897
Hi Diane,
the point is that the pivot total is the Expression total and not the total of all the rows in the chart, I've tried to show the difference below.
Example:
Fact Table:
%KeyFieldProduct, Value
1 , 10
2 , 20
3 , 30
Dimension Table:
%KeyFieldProduct, ProductName
1 , A
1 , a
2 , B
3 , C
Simple example but if you use ProductName as a dimension in a straight table and use sum of rows (with an expression of Sum(Value)), the result will
A 10
a 10
B 20
C 30
Total 70
Where as in a pivot table, the total will give you the expression total, in this example 60, because that is the absolute sum of the Value field, regardless of the duplication issues caused by the product field
To get the two charts aligned I would need to join in the product field to the fact table and duplicate up values (not recommended) or clean up my product field to remove the duplication
Hope that helps
Joe
Hi All,
Have tried the 'AGGR' function, as Joe suggested. Works out the calculations for each row, but I don't get a total in my pivot table.
Have attached JPEG.
Thanks
Di
Hi All,
Sorted thank you!
Used the AGGR function, but didn't add the 'SUM' before it!
Thank you!!!
Di
Hi, could you share a print screnn with us of your table and expression
Thanks
When I used the
=Aggr(Sum({< $(vSetView)>}POKTND),Famille, CDR, LB, CODE_PROJET, [Month of date],PO_FOURNISSEUR,PO_NUM_COMMANDE,PO_ACHETEUR, [Nom du Projet], PO_COMMENTS )
vSetView to select YTD/mtd
My result table is :
I don't know why I didn't see all months displayed (because I am slecting YTD and month = June , the total does not exist