Display data from the Grand Total column of a Pivot Table on a Stacked Pivot Chart
HTML-код
- Опубликовано: 26 июн 2014
- In this video, I'd like to share the process of "Displaying data from the Grand Total column of a Pivot Table on a stacked Pivot Chart" using Microsoft PowerPivot and CUBE functions.
I have blogged about this problem at this link on my website - www.ashishmathur.com/display-d...
![NoCap - Yacht Party [Official Music Video]](http://i.ytimg.com/vi/rm_lvDln8kE/mqdefault.jpg)
You are a genius, thank you greatly for your help, you have solved my issues. After scouring Google for a while, this is the absolute best method for doing just this task. Microsoft really need to add in a way for us to do this easily, hopefully that comes soon enoough. To add, you've also shown me (perhaps without realising); that if my data set is static, e.g. for a one-time graph, I can copy/paste my pivot table and create a pivot chart from that pasted value data. That has allowed me to utilise Grand-totals within the graph. If the dynamism is required, the rest of the video helps solve that issue. Excellent video and thank you for helping me out.
You are most welcome. Thank you for your kind words.
Thank you so much! Your video really helped me to solve something i've been struggling with all day! :)
You are welcome.
thanks for uploading this vidoe, it really helped to solve my problem
Thank you so much for such a detailed explaination!
You are welcome.
This is a really excellent video - well done Ashish. I had a problem similar to Lyn Tan. The Slicer information was not being included in the CUBEVALUE formulas. I think it was because I was slicing on the same field as the pivot table column - for example the Product field. I got around this by adding a new column to my PowerPivot table which was a copy of the Product column and then I sliced on the new column instead. It's all working beautifully now :)
+Catherine Kerr You are welcome. Thank you for your kind words.
Awesome! thank you! Please keep enlightening us!
You are welcome. Thank you for your kind words.
This video really helped me! Thanks a lot!
You are welcome.
great video with clear explanation! solved my problem :)
+Jithin Joseph You are welcome.
Thank you so much. This was helpful and educative.
You are welcome.
It was excellent sir. I was looking for same solution since 2 months..
You are welcome.
Thanks very much for the video.
Another simple way to add labels in the chart that gets automatically updated is to add text in the chart with value that points to the pivot table, e.g. Add Text with value ='Projects overview tables'!$B$129, where 'Projects overview tables'!$B$129 is the cell in the pivot table with the info you need to display, e.g. =CONCATENATE("TOTAL: ",C145," projects")
Hope it helps somebody looking for an easy way out.
Hi,
You are welcome. I did not understand - please share a more detailed explanation.
Ashish Mathur
Hello. Using Office 2010, from the Insert tab, I have added a Text Box (graphic element) in the chart where I need to display the figure with the Total. Then I have given as value of the Text Box what explained above. No much to add. Regards
Francesco Feliciani Thank you for sharing.
Thank you boss!!
You are welcome.
Great stuff from one video, slicer is a bonus
+Mike Lekar You are welcome.
Thanks, helped me to do my work easier today :)
You are welcome
Great video, thank you! May I ask you how to get the "Converted to Formulas" table dynamically changes its shape when you change the shape of the original pivot table (for example while adding a new field to the row or column layer...)? Or is this converted table just a static shaped copy with dynamically changed values due to slicing?
Thank you. You will not be able to do that.
thank you for sharing
+j so You are welcome.
Hi Ashish, very clear video. Should Excel 2013 automatically include slicers in the CUBE formula when you do the conversion at 8:11? Mine does not, and if I add the slicer field (which Excel recognizes as soon as I start typing) all I get is #VALUE!. Is there something I should install or enable? Thanks!
+Rodrigo Torres Hi, Thank you. Nothing to be installed at all. That should happen automatically. I need to see the workbook.
HI Ashish - this makes a lot of sense. What if you had a slicer that had the ability to toggle between all 4 quarters of the year. In my situation (and after following your excellent guidance) I have 36 months of data with 6 series including the grand total. If I click on the first quarter slicer for 2014, it shows the bars for Jan, Feb, March 2014 and their data correctly, but still displays all other months (lets say in this case June 2013) on the horizontal axis with non existent bars. Is there a way the chart can dynamically omit horizontal axis row labels that aren't applicable to the slicer selection (i.e. omit everything outside of Q1 2014)?
Hi,
Thank you for your kind words. Upload your workbook to OneDrive and share the link of the workbook here.
Ashish Mathur Hi Ashish - thanks for reaching out. Unfortunately I'm unable to since the spreadsheet information is connected to a secured database. If you removed all the April data from your source spreadsheet and click on one of your slicers you should find the same issue I'm facing..
Paul McE Hi. I delete the 3 rows of April from my source data worksheet and refreshed the Pivot Table. This caused the CUBE formula table to refresh as well. However, a row with #NA's appears. You may filter out the #NA rows from the CUBE formula table. The Pivot chart should appear just fine.
It seems to work only if the number of rows (month names) is static. What if months were also dynamically set in slicer. Is there a solution for this case?
I don't think so. One will have to manually drag the formula down
I excellent video
Do you have a video on how to format the data table say which might be showing a grand total column to bold ?
Thank you. Just format that row/column to Bold.
Ashish Mathur
Sorry I should have clarified that by data table I meant the one that you activate within a graph
I don't think I can solve that.
Hi, I did the same but slicer not impacting the below formula table values. Its remain same, What mistake I Did?
Hi. Cannot say unless i see the file.
Hi Ashish,
When I copy my pivot table, the slicer doesn't go with it. I noticed in your formula that there was ",slicer_region1,slicer_area1" after the cubevalue... Mine doesn't have this, so I tried to manually add it, but I get a #VALUE! error. Any guidance how I can get it to use the slicers in the formula?
Hi. I'll need to see your file.
The slicer doesn't seem to work in the pivot after converting to formula. Any suggestions?
It should. Share the link from where I can download your workbook.
Most of the audio is very low and i had to focus my attention.otherwise it a well presented and usefull information
Murali Chandran Thank you. For further videos, I will increase the volume while recording
i do the exact same way. but when i filter my pivot table after converting to formulas for the copied table, the copied table does not move according to the filters. it stays there. please help.
+Lyn Tan Hi. Upload your workbook to OneDrive and share the link of the workbook here.
+Ashish Mathur sorry i cant its a confidential file. any ideas why? because my month isnt fixed. it moves according to the product i filtered. like some product only some months will have.
+Lyn Tan Hi. Please create a dummy dataset and share that workbook.
thanks for the video but why tf is it so complicated to do that simple task microsoft should get their shit together