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

    Problem with price-volume analysis

    Nikita Chumakov

      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?