Adelina has attended:
Excel Advanced course
Excel Dashboards for Business Intelligence course
EXCEL, Pivot Tables
Hello,
I am using both versions of Excel 2010 & 2016 and having trouble to get Grand Total absolute number in Pivot tables. I have both buys and sales and Grand total is showing me net only instead of Gross. Is there a way to calculate gross grand total. Thanks
RE: EXCEL, Pivot Tables
Hi Adelina,
Thank you for the forum question. You can create a calculated field, but it will only work if your pivottable isn't grouped.
I have found a good video on Youtube.
https://www.youtube.com/watch?v=-sirg1EcMwQ
Call your calculated field Gross and if your field names are Buys and Sales the formula should look like this:
=Sales-Buys
You will get the Gross field displayed on your field list and then you just need to add it as a Value.
I hope this makes sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: EXCEL, Pivot Tables
Thank you for the prompt response Jens. I forgot to mention that I am using BI cubes some live database and cannot create a calculated field. Maybe when I attend the next excel course we can go through an example.
RE: EXCEL, Pivot Tables
Hi Adelina,
Where do you store the cube? If you store the cube in Excel you will have to open PowerPivot and calculate Sales-Buys. If you have both columns in the same table you can just add a column and do the calculation in Power Pivot. If you have the two columns in different tables you need to do a DAX measure in one of the tables.
If you explain me a little bit more about your cube I can be a better help.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
