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 » Pivot Chart from Powerpivot
Pivot Chart from Powerpivot
Resolved · High Priority · Version 2013
Sun 8 Apr 2018, 21:27Delegate Tsudoi said...
Tsudoi has attended:
Excel PowerPivot course
Excel VBA Introduction course
Pivot Chart from Powerpivot
Hi,
Please advise how to show Grand Total in pivot chart made from power pivot without copying & pasting the original data table by edit link or OLAP. I would like to use the pivot chart as normal but also show Grand Total values.
Thanks,
Mon 9 Apr 2018, 20:48Trainer Jens said...
RE: Pivot Chart from Powerpivot
Hi Tsudoi,
Thank you for the forum question.
Well I can only find paste link and Cube function options to do this.
But if you create the PivotTable from Power Query, you can create a calculated item and get your grand total in your chart.
Please have a look at the attachment.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best 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
Attached files...
GrandTotalsChart.xlsxSun 15 Apr 2018, 23:24Delegate Tsudoi said...
RE: Pivot Chart from Powerpivot
Hi Jens,
Sorry for replying late.
I went to Power Query Editor to see what/how you did there but it said compatibility issue. The pivot table was made by a newer version of Power Query where I use 2013. Nothing I can do about that? I would like to see how you used Calculated Item by Power Query to have Grand Total.
Thanks,
Mon 16 Apr 2018, 09:10Trainer Jens said...
RE: Pivot Chart from Powerpivot
Hi Tsudoi,
The calculated items is calculated in the PivotTable not in Power Query.
If you select one of the column headings in the last PivotTable (1000 Revenues, 2000 Cost of Sales, 3000 Other Income, or 4000 Expenses) and on the Analyse tab click Fields, Items, & Sets in the Calculation group and from the drop down click Calculated Item. If you select Total from the Name: drop down list you will find the formula in the Formula: box.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best 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
Mon 16 Apr 2018, 10:18Delegate Tsudoi said...
RE: Pivot Chart from Powerpivot
Hi Jens,
Thanks again for your reply.
I thought so too but then Fields, Items, & Sets is grayed out and I cannot see Calculated Item and any detail further.
I know this happens if used in PowerPivot but I am not sure if that's the case here.
Regards
Mon 16 Apr 2018, 10:30Trainer Jens said...
RE: Pivot Chart from Powerpivot
Hi Tsudoi,
Please find attached file without Power Query.
Please do as my last answer.
You cannot use Calculated Items if you have groupings in the Pivot Table.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best 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
Attached files...
Mon 23 Apr 2018: 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:Reconciling a list to correspond with anotherMay have a list that needs to make but on another sheet one list seems to be out, eg. product list one sheet contains all product and inventory data, while the other contains pricing data. Both need to match with all products but there is more products in one list than the other. To find the disparaging product compare data in the two columns that need to match Make sure that order the same way. |
