1 Reply Latest reply on Mar 3, 2012 12:21 PM by Shawn Wallwork

    How do i get the SUM of Averages with respect to a particular Field?

    Archana Amarnath

      Hi,

      I have discount and Sales data for a set of Products (SKUs).

      • The Discount information
      • is at a SKU- Region- Reason (Reason for discount) level
      • The Sales Information is only at a SKU- Region Level, and therefore repeats for every Reason for a particular SKU-Region

       

      Refer to the Example below:

      SKU    REGIONREASON (for discount)Discount AmountSales Amount
      SKU 555CL RS 1202000
      RS 2252000
      RS 3302000
      RS 4352000
      RS 5402000
      GURS 3501000
      RS 4601000
      RS 5701000

      Discount Rate= sum of Discount Amount/(Sum of Discount Amount + Sum of Sales)

      Discount Rate for SKU 555, Region CL,Reason RS 3 = 30/(30+2000)= 1.48%

      Discount Rate for SKU 555 = sum of Discount Amount/ (Sum of Discount Amount + Sum (Avg of Sales for a SKU- Region))

      i.e. 330/(330+ (2000+1000))= 9.9%

      I had tried to use Window Sum to get the numbers we need, but it did not serve the purpose.

       

      Am attaching:

      • Tableau File that i have been working on
      • Excel File that contains the Backend data used, along with two pivots for QC of the Numbers

       

      The Discount Rate percentages in the Tableau File Sheet 2 is not what we need.

      Eg: For SKU 1, Discount Rate= 25.43%, whereas we need it to be 1.92% (Refer to Excel Sheet for the Calculation)

      Basically, we need to find a way to average the Sales Amount by SKU-Region, and then add up the Averages.

       

      Need some help here!

      Thanks,

      Archana