98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » EXCEL, Pivot Tables
EXCEL, Pivot Tables
Resolved · Low Priority · Version 2016
Thu 18 Apr 2019, 16:56Delegate Adelina said...
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
Thu 18 Apr 2019, 19:11
Edited on Thu 18 Apr 2019, 19:12Trainer Jens said...
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
Thu 18 Apr 2019, 22:22Delegate Adelina said...
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.
Fri 19 Apr 2019, 15:34
Edited on Fri 19 Apr 2019, 15:34Trainer Jens said...
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
Fri 26 Apr 2019: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Paste functions box quicklyIf you want to do a formula using the paste functions box press SHIFT + F3. |
