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?
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.
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" ?
1) Correct, "[historic avg discount]" already exists in the source data per department.
2) Correct, [Actual discount]/[historic avg discount]. Lets call this AVG-I
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([AVG-I]*[SALES])/SUM([SALES])
The problem i am running into is that [AVG-I] is already aggregated and the resulting number should be the same for the entire store. (.91 in the example here)