Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]))
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]))
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] ) )
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]))
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.
Hi Sunny,
Yes this works. Many thanks for the solution!!