I have a workbook (see attached) where I'm struggling to do 2 things:
In customer price over time tab, I'm trying to get 2 different lines that are the weighted average for the account type in that quarter. I backed my way into a solution on the revenue/unit over time tab but it's not letting me do the same solution in the customer price over time tab due to what I think is an aggregation issue. When I add a reference line, the only thing it's returning is the unweighted average customer price (i.e. 1 order of 100 units at $1,000 and 1 order of 1 unit at $1,500 gives $1,250 as average) and that's only for the quarter as a whole.
On both tabs, I would like to add "Warranty?" as a filter on both these sheets, but this field is grayed out. Not all orders have a warranty, but each individual order and each individual customer only has 1 warranty. Yet the field is grayed out and it won't allow me to add it as a filter (or even in the detail). Additionally, when I break out the warranties on another sheet, Q1 warranties show with a * rather than broken out by warranty. I don't think I can use Upcharges as the primary data source because, as I said, not every order has a warranty, so if I used upcharges as a primary I don't think all my orders would show, only those with a warranty.
If anyone could provide some help, that'd be much appreciated.