How to add target line to a pivot-chart?

Question

I have a resource allocation report that automatically generates each week based on a bunch of monthly data. The data is summarized in a PivotChart with slicers for different column values (Name, Role, Project, etc). The chart shows the units allocated for each role, for each month for the next 13 months: Resource Allocation PivotChart

Basically, I want to add a target line to this chart. I've tried a couple different things.

With other projects, I've succeeded in adding a target to PivotTable data by adding the target value in a column next to the PivotTable, but that won't work with this report because that method forces me to use a regular chart instead of a PivotChart so I can't have the slicers (which I need), and the PivotTable changes shape a lot so it would overwrite the data as different slicer options are selected and as new values are added to the data each week.

The closest I've gotten to adding the target into the chart is by actually creating a new line item where the Role is "Target" and the value for each month is the target value: Target line item. I can even change the data series type to look like a target line: PivotChart with target line. The only issue is that "Target" now shows up in the role slicer.

Is there any way to hide "Target" from the Role slicer when it HAS data? Or is there some other way to add a target line to this PivotChart?

Answer 1

You could create a grouping column where RoleGroup is the same for both the Role and the Target, and use that for your slicer instead. That way you are effectively selecting Role and Target at the same time for any set of values.

Another alternative would be to convert your data to a "long" format, that looks something like below, and then you can drop Value and Target on the Measure shelf of the pivot table: