
1. Re: Index to avg table calc
Ken Patton Aug 12, 2015 4:41 PM (in response to Amy Mandler)Hi Amy,
No hablo Excel. What does the Excel SUMPRODUCT function actually do? Tableau uses the word "Index" to mean something specific, which appears to be different than what you are doing.
When you say your .91 is weighted, are you weighting it by the "Sales w/ discount" or some other measure?

2. Re: Weighted avg table calc
Amy Mandler Aug 12, 2015 5:40 PM (in response to Ken Patton)Ken, Sum product is used to get a weighted avg. Here it will multiply % discount * sales for each row and add it together.
Sorry for the use of Index, yes quite confusing. I am not talking about the tableau function, it is just a name of a variable I want to create.

3. Re: Weighted avg table calc
Ken Patton Aug 12, 2015 5:50 PM (in response to Amy Mandler)Okay I think I follow.
So... "Historic avg discount" is a data element in your Data Source already reckoned at the Department level ?
And you compare that as a denominator, to a sorta current discount as a numerator?
And the number you want help computing would be an average of that, weighted by the "Sales w/ discount" ?

4. Re: Weighted avg table calc
Amy Mandler Aug 12, 2015 6:08 PM (in response to Ken Patton)1) Correct, "[historic avg discount]" already exists in the source data per department.
2) Correct, [Actual discount]/[historic avg discount]. Lets call this AVGI
3) In essence I want a number for the entire store (same for all departments). And yes, this is weighted by the $$ value in actual sales for each department.
The calculation should be SUM([AVGI]*[SALES])/SUM([SALES])
The problem i am running into is that [AVGI] is already aggregated and the resulting number should be the same for the entire store. (.91 in the example here)

5. Re: Weighted avg table calc
Ken Patton Aug 12, 2015 6:14 PM (in response to Amy Mandler)Okay here's a solution. There may be better ones out there, but hopefully it exposes the logic.
Look for the fields with "LoD" in the names.

Index to avg example  lod.twbx 43.4 KB
