5 Replies Latest reply on Aug 12, 2015 6:14 PM by Ken Patton

    Weighted avg table calc

    Amy Mandler

      I am essentially trying to create a table calculation similar to a weighted avg, but am running into issues due to aggregations.

       

      I have store data and a historic discount %, then in tableau I calculate and actual discount %, and Index based on the department. I am trying to calculate one universal number for all the departments that is a weighted avg based on the sum of total sales and the index by department (table below)

       

      Capture.JPG

        • 1. Re: Index to avg table calc
          Ken Patton

          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

            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

              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

                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)

                • 5. Re: Weighted avg table calc
                  Ken Patton

                  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.