I am creating a pivot graph showing actual expenses vs budgeted, I'm having issues with the labels. I'm trying to get the labels to sit on the outside of the budget bar so I created a new calculated line for the labels and it worked perfectly... until I used the slicer to change the dynamic of the bar graph, then the totals were all sorts of wrong! If I set the labels to capture the entire row of information it captures subtotal information that is not needed for the bar graph (I should mention the vertical axis is grouped). I don't know if I'm explaining this very well, so attached is a screenshot of my issue.
Hi Jessica,
Please upload the file or if the data is sensitive you can upload a sample file that illustrates the problem.
Mynda
Here is a sample file of my dashboard. Thanks so much for your help.
Hi Jessica,
It's not clear what field you want the labels to display:
- Sum of 2021 Budget
- Sum of 2021 YTD
- Sum of Label
You mention the subtotals causing problems, but you can turn them off. Select one of the subtotal labels in the PivotTable i.e. cell A5 or A10 > right-click > select Subtotal "Group" to turn them off.
If you're still having trouble, please clarify what values the labels should be displaying. Keep in mind that it needs to be clear to the user, which is tricky when you're only labeling one series out of many.
Mynda
Hi Mynda,
I would like to turn on the data labels for Sum of Label but have them display the Sum of 2021 YTD totals. I turned off the totals, and it's still not displaying like I would like to see.
Hi Jessica,
Thanks for clarifying. You can't do this with a Pivot Chart. You need to use the Value From Cells label option, but a PivotTable will only show the labels that belong to that series i.e. Sum of Label, as opposed to another series being Sum of 2021 YTD Totals. You'd need to build a regular chart from the PivotTable data.
Mynda
By jove that fixed my problem!! Thank you so much! I was starting to get frustrated!
