Tsudoi has attended:
Excel PowerPivot course
Excel VBA Introduction course
Pivot Chart (powerpivot)
Hi,
Please can advise how to create a pivot chart with stacked columns that contain monthly breakdown and have months in Axis?
In other words, as time passes, total is an accumulative of past months within stacked column chart and I'd like to see that the progress over every month having month in Axis.
I hope that makes sense?
Thanks,
RE: Pivot Chart (powerpivot)
Hi Tsudoi,
Thank you for the forum question.
Can I please ask you to Google Waterfall chart and tell me if it is something like this you are looking for.
Thanks.
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: Pivot Chart (powerpivot)
Hi,
Not really. I am not looking for waterfall chart.
All I am looking for is a simple stacked column chart to show sales over months but each month sales figure is also shown within each stacked coloumn, showing the total as accumulative.
I hope that makes sense.
Regards
RE: Pivot Chart (powerpivot)
Hi Tsudoi,
If I understand you right, there is only one way of doing it.
Please have a look at the attached file. I haven't used a PowerPivot data model but you can extract the total using the cube functions from your data model. It is not as smooth solution but you are only working with 1 dimension in you output to the chart and stacked charts needs 2 dimensions.
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
Attached files...
stackcumulative.xlsxRE: Pivot Chart (powerpivot)
Thanks for your reply.
I understand what you did in terms of having Month in both X and Y axis but how can I do that in pivot chart? I have the chart made in PowerPivot and it has to be a pivot chart as it needs filtering criteria on the chart itself or at least a slicer.
It'd be easy enough if I could just make small table mannually but I have a large amount of data which I need to make in pivot table/chart and apply filtering to see particular results.
Are you able to advise further to achieve that?
Thanks,
RE: Pivot Chart (powerpivot)
Hi Tsudoi,
As I mentioned in my answer. You will have to use cube functions from your data model to extract the data. We did it on the course. The cubevalue function will also be filtered by the slicers.
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: Pivot Chart (powerpivot)
Hi,
If Cube function is used, every time new data are added, I have to recreate pivot table and convert to cube function table?
If so, it's just not practically possible to use it..?
Regards
RE: Pivot Chart (powerpivot)
Hi Tsudoi,
You can still use your PivotTables, but for the special chart you want you need the two dimension table as in my example. Keep your PivotTables in your report. On a new sheet create the two dimension table with months going down and across (make sure that you reference the months the same way as in your data model "Jan", "January", or "Month 1"). Use Cubevalue function filtered by your slicers and Month.
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
