3 Replies Latest reply on Aug 11, 2016 1:38 AM by Cyrille Thybert

    Calculating Segment with Weighted average price

    Cyrille Thybert

      Hi everyone,

       

      I have issue to solve a calculation (certainly using LOD can solve this).

       

      I want to calculate segment through Weighted Average Price (WAP), let me explain  :

       

      My perimeter is simple, I have different kind of product sold over a year with different prices and each product can have a evolving price.

       

      Dataset sample :

           

      DateProductPriceSales UnitSales Value
      01.04.2016product 1101041040
      01.04.2016product 2152583870
      01.04.2016product 3202865720
      01.04.2016product 425571425
      01.05.2016product 1111181298
      01.05.2016product 2163104960
      01.05.2016product 3213587518
      01.05.2016product 426641664
      ...............

       

      I calculate the WAP (over all product) using this formula :  sum([Sales Value])/sum([Sales Unit])

       

      I want to create price segment :

       

      LOW = below the WAP

      HIGH = above the WAP

       

      And recalculate the

      WAP of the price segment LOW : sum([Sales Value])/sum([Sales Unit]) where selling prices of products are below the main WAP

      WAP of the price segment HIGH : sum([Sales Value])/sum([Sales Unit]) where selling prices of products are above the main WAP

       

      And be able to show in a dashboard : HIGH WAP + MAIN WAP + LOW WAP.

       

      I try using this formula to get WAP HIGH :  if [Price]> ({fixed [Date] : sum([Sales Value])}/{fixed [Date] : sum([Sales Unit])}) then sum([Sales Value])/sum([Sales Unit]) end

       

      But I have calculation aggregation issue. If I replace the sum([Sales Value])/sum([Sales Unit]) by [Sales Value]/[Sales Unit] I don't have calculation issue but the result is wrong.

       

      Do you have any idea how to solve this ?

       

      Please find attached a workbook with the dataset.

       

      Thanks,

      C.