I have sales data by market offering. Every quarter each offering can have multiple deals. There is a target per quarter for each offering.
I want to show a pivot/chart and have a slicer for market offering, so when you click on a Market offering you get the actuals for that market offering as well as the target. I was able to achieve this by adding a new column called target for each sale record and then showing Average of Target.
However, when I clear the slicer, I want to show the overall actuals (across market offerings) with the overall target.
So Target has to be Sum(Average of Target by Market Offering). I am getting Average of Average of all Market Offerings instead.
How do I solve this?
Attached is a sample sheet.
I want to show a pivot/chart and have a slicer for market offering, so when you click on a Market offering you get the actuals for that market offering as well as the target. I was able to achieve this by adding a new column called target for each sale record and then showing Average of Target.
However, when I clear the slicer, I want to show the overall actuals (across market offerings) with the overall target.
So Target has to be Sum(Average of Target by Market Offering). I am getting Average of Average of all Market Offerings instead.
How do I solve this?
Attached is a sample sheet.
Last edited: