5 Replies Latest reply on Dec 10, 2017 9:46 PM by Zhouyi Zhang

# Gross margin driver analysis - Calculation with aggregate and non-aggregate

Hi Tableau experts,

In the attached workbook (Tab GM impact), I am trying to calculate the GM impact of cost/kg, net sales/kg and vol mix change. The formulation would like below

- PY GM % = PY period profit/PY period net sales

- GM% impacted by cogs/kg change = 1 - sum(CY cost/kg * unit*size(kg))/sum(PY net sales) - basically the gross margin should we replace the PY cost/kg with CY cost/kg while keeping price and volume the same. Cost/kg is calculated as average cost/kg of products with same category, channel, size(kg), market and Flavor.

In which, CY cogs/kg = Sum(CY net sales - CY profit)/sum(CY volume). For this calculation, the CY cogs/kg should be by product, which is identified by category (Either A, B, C, D, E), Channel, Size (kg), Market and Flavor.

However, since CY cogs/kg is an aggregate calculation, the below errors appeared and blocked me from the calculation. Any idea how I could perform this calculation?

- Also when looking at Datasource, I saw the value of CY period cogs/kg in every row (either null or a specific value). IS there any way to see the calculation of those value? And why couldn't they be used as normal data when performing calculation?

Thanks,

An

• ###### 1. Re: Gross margin driver analysis - Calculation with aggregate and non-aggregate

Hi, An Nguyen

I think to fix the calculation aggregation - non aggregation, you need put the unit*size before the data gets sum(), otherwise, it doesn't make sense to your data.

However, I don't know what should be the correct result, because the calculation above shows 100%.

ZZ

• ###### 2. Re: Gross margin driver analysis - Calculation with aggregate and non-aggregate

Hi Zhouji,

"CY period net sales" is not an aggregate one, as it is the result of "IF" function. I need to use "CY period NS/kg" or "CY period cogs/kg", and multiply with (unit*size), and Tableau could not execute the formula.

An

• ###### 3. Re: Gross margin driver analysis - Calculation with aggregate and non-aggregate

Hi, A Nguyen

Try this

1-{ FIXED :[CY period NS/kg]}*[Size (kg)]*[Units]/{ FIXED :SUM([PY period net sales])}

ZZ

• ###### 4. Re: Gross margin driver analysis - Calculation with aggregate and non-aggregate

Thanks Zhouji for your effort. However, the {FIXED: [CY Period cogs/kg]} does not work well, as each product has different average cost, and cannot use the same data for the whole dataset.

An

• ###### 5. Re: Gross margin driver analysis - Calculation with aggregate and non-aggregate

Hi, An

I didn't see any column/field related to product, but if it exists in your real data, then you can try

1-{ FIXED [product]  :[CY period NS/kg]}*[Size (kg)]*[Units]/{ FIXED [product] :SUM([PY period net sales])}

Hope this could help

ZZ