Set Analysis: Aggr sum and count

[Kat92]

Hi,

I have created the following calculation and want to calculate the difference in sum of Goal and count of Actual. What I need is to calculate this difference in ID level and then sum the difference and report in the ColumnI levels. Finally in case this is negative want to replace with 0.

if(
(Sum(Aggr(sum( {< Category={'Plan'}>} [Goal]),
[Column1], [Column2],[Column3],[Column4],ID)))
-
Sum(Aggr(Count(DISTINCT {< Category={'Calls'}>} [Actual]),
[Column1], [Column2],[Column3],[Column4],ID))>0
,
(Sum(Aggr(sum( {< Category={'Plan'}>} [Goal]),
[Column1], [Column2],[Column3],[Column4],ID)))
-
(
Sum(Aggr(Count(DISTINCT {< Category={'Calls'}>} [Call Name]),
[Column1], [Column2],[Column3],[Column4],ID)))
,
0)

The problem is that even if the calculated outcome for the 2 separate seems correct when I do the substraction it shows less than what should.

Could someone please assist?

Thanks in advance

[TcnCunha_M]

could you please explain a little bit better what you mean by 2 separate?
if you try to do like this

if(
(Sum(Aggr(sum( {< Category={'Plan'}>} [Goal]),
[Column1], [Column2],[Column3],[Column4],ID)))
-
Sum(Aggr(Count(DISTINCT {< Category={'Calls'}>} [Actual]),
[Column1], [Column2],[Column3],[Column4],ID))>0
,
1,
0)

And tell me if have 1 and 0 in the right place.

[marcus_sommer]

You may try:

rangemax(0,
Sum(Aggr(
   sum( {< Category={'Plan'}>} [Goal]) - Count(DISTINCT {< Category={'Calls'}>} [Actual]),
[Column1], [Column2],[Column3],[Column4],ID)))