Warpath Mechanic
New Member
I have scoured the internet and these forums and for the life of me I cannot seem to find an answer to what I had thought was a simple problem.
I have a list of phone calls from an automated transcription service that lets me know if the call resulted in an appointment from one of several vendors:
Fiscal Week Call Affiliate Result
45 1 Guy1 Yes
45 2 Guy1 Yes
45 3 Guy2 No
45 4 Guy2 No
46 5 Guy1 No
46 6 Guy1 No
46 7 Guy2 Yes
When I create the pivot table. I have the Fiscal Week as the Row Label and the Affiliate and the Result as the Column Labels. I set the field values to "Show As...% of Parent Column Total" which gives me EXACTLY what I need. I set up my Pivot Chart. Everything is ok so far.
The problem arises that the graph is messy. I really only care about the percentage of "Yes" results on the graph and therefore do not need to show the percentage of "No" values.
If I filter the "No"s out of the pivot table, my percentages are wrong. I am uncertain if there is a way to accomplish this with pivot tables/pivot charts. I am open to VBA, Conditional Formatting, etc. The real benefit is that my supervisor likes to have the slicers available for easy selection and view of what the charts represent.
If this cannot be done with Pivot Tables/Charts/Slicers, then I am certainly open to suggestion.
If anyone can solve this. I know you guys can. If asked I can produce dummy data on an excel sheet. I would post the grand sheet but major corporate security risk.
I have a list of phone calls from an automated transcription service that lets me know if the call resulted in an appointment from one of several vendors:
Fiscal Week Call Affiliate Result
45 1 Guy1 Yes
45 2 Guy1 Yes
45 3 Guy2 No
45 4 Guy2 No
46 5 Guy1 No
46 6 Guy1 No
46 7 Guy2 Yes
When I create the pivot table. I have the Fiscal Week as the Row Label and the Affiliate and the Result as the Column Labels. I set the field values to "Show As...% of Parent Column Total" which gives me EXACTLY what I need. I set up my Pivot Chart. Everything is ok so far.
The problem arises that the graph is messy. I really only care about the percentage of "Yes" results on the graph and therefore do not need to show the percentage of "No" values.
If I filter the "No"s out of the pivot table, my percentages are wrong. I am uncertain if there is a way to accomplish this with pivot tables/pivot charts. I am open to VBA, Conditional Formatting, etc. The real benefit is that my supervisor likes to have the slicers available for easy selection and view of what the charts represent.
If this cannot be done with Pivot Tables/Charts/Slicers, then I am certainly open to suggestion.
If anyone can solve this. I know you guys can. If asked I can produce dummy data on an excel sheet. I would post the grand sheet but major corporate security risk.
Last edited:
