2 Replies Latest reply: Oct 1, 2015 12:05 PM by Michele Becchio RSS

    Help with Pivot Table


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


      ManagerISOProductNet WeightImport Euro



      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!