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

    AGGR Function Help.

    Adam Hughes



      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:



      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]))




      Any help is appreciated.