6 Replies Latest reply: Apr 18, 2017 5:34 PM by David Hanaway RSS

    Problem with price-volume analysis

      In my data model products are grouped into brands and I have built pivot table where I am analysing price/volume variances:


      21-11-2012 11-42-01.jpg


      The formulas which are behind Price and Volume effects are as following:


      Volume effect =([QTY CY]-[QTY PY])*[PY YTD]/[QTY PY]/[PY YTD]

      Price effect =(([CY YTD]/[QTY CY])-([PY YTD]/[QTY PY]))*[QTY CY]/[PY YTD]


      where CY - current year, PY - previous year


      expression is calculated correctly only on product level and is wrong at Brand total level (illustrated in excel file attached). How can I implement PVM formula to show correct +6% effect of total brand level?