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.