4 Replies Latest reply on Dec 23, 2016 9:28 AM by scott.kirk

    Weighted Average over time w/ Grand Total

    scott.kirk

      Hi all. Relatively new user here, so apologies if this is something obvious I'm missing.

       

      I've attached a sample workbook which represents a subset of the data I'm working with. The "Stats" sheet shows weighted (by Sales) average by client and overall weighted average for a given month using table calcs. In this view I can look at one or multiple client averages vs. overall averages for a given month. On the second sheet, "Measure3 Over Time" I'm trying to replicate these views but see how they are trending over time. So again, look at a single client or multiple clients vs. overall weighted averages (2+ lines) over many months. How do I get the "All clients" (Grand total) line to show, and show the correct average that matches the first sheet?

       

      I've played around with it for a bit, but was never able to get the correct values when selecting more than one client filter. I'm assuming it has to do with my table calc, but can't seem to pinpoint. Note: my As_of_Dates are discrete, monthly values. Also, as part of my data set I have columns for each measure that hold a plan's weight for a given month. The sum of these fields for a given month would represent the weighted average across all plans. Maybe a way to utilize these to accomplish what I need?