Hi guys,

I am trying to get a function to work, where I essentially want the sum to be calculated based on a key which needs to be distinct.

Table looking like this:

ID Type Component Group Actual ValueKEY

X Cost Internal 0,85 X0,85

Y Cost Internal 0,85 Y0,85

X Target Internal 1,25 X1,25

Y Target Internal 1,25 Y1,25

What I am trying to achieve is that it is possible to calculate the actuals once based on the dimension "ValueKEY", so that for both these instances the result will become:

ID Actual Target

X 0,85 1,25

Y 0,85 1,25

I've tried to set it up like this, where YTD_TY is an expression for the fiscal year, and the vCostComponents are the cost components and vAccum = "RangeSum(Above(", and v/Accum = ,"0,RowNo(TOTAL)))" that I am interested in:

$(vAccum) sum ({$<[_YTD_TY] = {'YTD'}, [Component Group] = {$(vCostComponents)}>} aggr(sum(Distinct([Actual])), ValueKEY)) $(v/Accum)

But this results that I am getting two values where the actuals are duplicates and by that I receive a too high figure.

I have also tried this one;

$(vAccum) sum({<[Type]={'Cost'}, [Component Group]={$(vCostComponents)},[_YTD_TY]={'YTD'}>} distinct [Actual])$(v/Accum)

This results in a figure that sums up to being a little too much. Can any of you help / spot what is wrong with my expression?

Thank you,

Stefan