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?

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

• ###### 1. Re: How do i get the SUM of Averages with respect to a particular Field?

Archana, I think the attached workbook is what you're looking for. I changed your formula from AVG() to SUM() and it produces the numbers the same as your spreadsheet:

Then use the sub-totals to show the category % discounts:

By the way, I'm pretty sure what you wrote here:

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

...is a typo. It is 4.09% not 1.92%, right?

Also, if you take the Regions off the row shelf you'll get the correct totals for each SKU:

Hope this helps,

--Shawn