AGGR Function Help.

[adamh2404]

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.

[Anonymous]

try:

sum(aggr(

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

),[Sales Cost (GBP)],[Sales Qty]))

/

sum(aggr(

IF(

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

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

),[Sales Cost (GBP)],[Sales Qty]))

[agigliotti]

try this expression below:

=Sum( Aggr(

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), [Product ID] ) )

[sunny_talwar · Accepted solution]

May be this

Sum({<[Ledger Year] = {2017}, [Product ID] = {"=Count(DISTINCT {<[Ledger Year]={2017, 2016}>} [Ledger Year]) = 2"}>} [Sales Cost (GBP)])

/

Sum({<[Ledger Year] = {2017}, [Product ID] = {"=Count(DISTINCT {<[Ledger Year]={2017, 2016}>} [Ledger Year]) = 2"}>} [Sales Qty]))

[adamh2404]

Hi,

Thanks but this doesn't work. This divides correctly at line level but then just sums the 2 answers rather than calculating the totals of each half of the expression and then dividing.

[adamh2404]

Hi Sunny,

Yes this works. Many thanks for the solution!!