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

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

    An Nguyen

      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