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

# Weighted Average over time w/ Grand Total

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?

• ###### 1. Re: Weighted Average over time w/ Grand Total

Scott,

Didn't look into your workbook yet so I apologize if this isn't what you're looking for.

Still a little fuzzy on what you need exactly. Could you maybe bold your main question or post a picture of your desired end goal?

In the meantime- you may be looking for this:

-bring grand total into view

-change how totals are calculated

You can use analytics pane:

or Analysis tab up top:

Using the analysis tab you can change how the totals are calculated. Have you checked this tab out yet?

• ###### 2. Re: Weighted Average over time w/ Grand Total

I've bolded my main question and attached an image of what I'm trying to accomplish on the "Measure3 Over Time" sheet in Tableau. Ideally, I'd like the ability to stack multiple clients vs. overall as well.

• ###### 3. Re: Weighted Average over time w/ Grand Total

So like this?

If so I will explain how to do it- If it needs tweaking let's work through it

Essentially, I just made a dual axis map- one with client on color, the other without on color so it sums all the clients into one line

• ###### 4. Re: Weighted Average over time w/ Grand Total

That is the view I am looking for but the values are not correct. Weighted average values for Measure3 for all months, all clients, and overall should be >100. You can see what the correct value for a client/overall for a given month would be by using the filters on the Stats sheet. All values there are correct.