9 Replies Latest reply: Oct 2, 2015 2:08 AM by RSS

    help with pivot table

    Michele Becchio

      Hi,

      I have a problem with a pivot table... in particular I have this rows of data (example below):

       

      ManagerISOProductNet WeightImport Euro
      RossiCLApple125
      RossiCLApple137
      RossiITPere102

       

       

      And I create this Pivot Table with these colums:

       

      ManagerISOProductTotal Net Weight I° CatTotal ImportAverage PriceTotal Net Weight II° Cat

       

       

      where the dimensions are:

      Manager, ISO and Product

      and the Expressions are:

      Total Net Weight = sum(Net Weight)

      Total Import = sum(Import Euro)

      Average Price = sum(Import Euro)/sum(Net Weight)

       

      The problem is How to calculate the column named Total Net Weight II° Cat...I would like to calculate the sum of kilograms where the average price of every single row is less than 50 % of the average price of the product calculated in column Average Price.

       

      I try to calculate with this expression bat I think isn't correct...

      Total Net Weight II° Cat = sum(if([Import Euro]/[Net Weight] < ( aggr( sum(Import Euro)/sum(Net Weight)/2), Manager,ISO,Product  ), Net Weight, 0)

       

       

      In particular I do not know how to set up an expression that allows to evaluate ( and include it in the sum ) every single detail line based on expressions already calculated previously as aggregation

       

      thank you for your support!

      Michele

       

      Messaggio modificato da Michele Becchio