5 Replies Latest reply: Nov 6, 2017 4:56 AM by Adam Hughes RSS

    AGGR Function Help.

    Adam Hughes

      Hello,

       

      I'm having a problem correctly calculating a value based on an expression using the aggr function.

       

      The data is as below:

       

                               Feb 2016          Feb 2017

      Product ID     Qty      Cost        Qty      Cost

      A                   null()    null()         2        13.85                               

      B                    3        20.05       10        59.00

      C                   18       102.13      38        201.09

      D                    5         28.36      22        116.42

       

      Now I need to divide the total Cost/Qty only where a product appears in both years.

      So for my KPI for Avg Cost for 2017 the calculation would be:

      (59+201.09+116.42)/(10+38+22) = 5.37

       

      I have this as a starting point, however that is not doing the check for both years at product ID level and is therefore calculating my KPI across all 4 products rather than 3:

       

      IF(

      Sum({<[Ledger Year]={2017}>}[Sales Cost (GBP)])>0 and Sum({<[Ledger Year]={2016}>}[Sales Cost (GBP)])>0,

      (Sum({<[Ledger Year]={2017}>}[Sales Cost (GBP)])

      /

      Sum({<[Ledger Year]={2017}>}[Sales Qty]))

      ,0)

       

       

      Any help is appreciated.