Ask a new question

Pivot Table Chart Slicer by Week Number

Hello!

I'm trying to create a dynamic pie chart from a pivot table of data and included a slicer to filter by week number. I want to compare spend on a weekly basis (by week number) or between multiple weeks.

When trying to filter by week number, the data reflected in the data label is being pulled from the 1st column shown when filtered. How do I have the data label reflect the total sum of the columns instead of pulling from the 1st column of the pivot table?

For example, I want to see the total categorical spend of weeks 29 & 30, but the data label reflects the categorical spend of week 29.

Hi,

Share the link from where I can download your Excel file.

Regards,

Ashish Mathur
www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Sophia
I am Vijay, an Independent Advisor. I am here to work with you on this problem.

Logically, it should show both. Request you to please prepare and upload a sample / dummy file sans confidential / sensitive data to Onedrive and share the link here.

HOW TO UPLOAD
1. Login to https://onedrive.live.com/ utilizing the same Login ID and Password which you have used on this forum. (or use the installed Onedrive on your computer)
2. Click Upload in the top OR drag and drop the file here.
3. After uploading, right click the file and choose share.
4. Click Copy Link.
5. Copy the link and paste the link here.
Sincerely yours,
Vijay A. Verma | Blogging @ https://excelbianalytics.com | Linkedin @ https://www.linkedin.com/in/excelbi/

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)

One pie chart of total sums for weeks selected randomly in Slicer.

http://www.mediafire.com/file/ovq88kxz3fyu119/07_29_19.xlsx/file

http://www.mediafire.com/file/zrdy42j2vha9mzk/07_29_19.pdf/file

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

The problem is not the data label, it's the chart at all, because the data in the chart is only from the first column. See screenshot below.

As you can see we have data for the label "C" and 2 weeks, but the chart did not show anything for C nor the data for week 2 (in this case B must be 9, not 8)!

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

So the only way out of this is to create a separate data section. See screenshot below.

K5: =IF(A5="","",A5)
L5: =IF(A5="","",SUM(B5:I5))
and drag down. Now create a pie chart from that data.

Andreas.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

There is one issue with this solution, if the Pivot table is filtered and the data section for the chart shows "" in the cell, a pie chart shows a 0 as data label and a colored icon in the legend.

The solution for this is to set a filter and filter out empty labels.

Andreas.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

If you have issues to rebuild the solution for your file, maybe it's helpful to have a look into my sample file:
https://www.dropbox.com/s/bpl4v9i58lficqm/a125a...

Do you have any questions?

Andreas.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 1,296 Applies to: