Sum only positive results of an array

[Kat92]

Hi,

I have created the following measure in set analysis and is working fine producing in all these columni dimensions the difference

Sum(

Aggr(

    RangeSum(

        Sum({< Category={'Cat1'}, [Measure1]={"<1"} >} [Goal]),

        -Count(DISTINCT {< Category={'Cat2'}, [Measure1]={"<1"} >} [IDs])

    ),

    [Column1], [Column2], Column3, [Column4], Column5, Date

))

The next step is to sum only the positive differences and ignore the negative ones in the total sum.

I have tried adding if cases in several places within the measure but does not work.

Could you please assist?

[Or]

Not sure what you mean by "Sum only the positive differences", but typically the way to "ignore" negative values in a sum is to use RangeMax():

Sum(RangeMax(Expression,0))

[Kat92]

what I mean is that in a table with all the dimensions the difference has a value, I want to ignore the negative ones.

I have tried rangesum but the total does not change

So in a example it will give 4 and not 3 for the following example.

Column1	Column2	Column3	Column4	Column5	Results
Val1	Val1	Val1	Val1	Val1	2
Val2	Val2	Val2	Val2	Val2	1
Val3	Val3	Val3	Val3	Val3	0
Val4	Val4	Val4	Val4	Val4	-1
Val5	Val5	Val5	Val5	Val5	1

[Or]

Looking at that, I believe it should be:

Sum(
RangeMax(Aggr(
RangeSum(
Sum({< Category={'Cat1'}, [Measure1]={"<1"} >} [Goal]),
-Count(DISTINCT {< Category={'Cat2'}, [Measure1]={"<1"} >} [IDs])
),
[Column1], [Column2], Column3, [Column4], Column5, Date
),0))

[Kat92]

I have tried this and the result is the result of Sum({< Category={'Cat1'}, [Measure1]={"<1"} >} [Goal]) this part.

As if it ignores the second measure.

[Or]

That... doesn't seem to add up? Why would a RangeMax() function placed outside the Aggr() in any way change the behavior of the aggr()'s internal expression? If that is indeed the case, I would expect something is wrong with the internal sum() itself and needs to be looked at, rather than an issue related to the RangeMax().

Are you able to share some sample data? Your table in the above post isn't enough, since it doesn't contain the underlying data required to calculate the expression (Category, Measure1, Goal, IDs, Date).

[Kat92]

I have attached a file with the data using.

As you can see the result column is my measure and it produces the differences of columns G and H.

What I want is the total to show 31 (exclude the cases where the H column is smaller than the G)

[Or]

In this case I had to make a few modifications to your expression because Date and Category1/2 are missing, and IDs is given as a result so I can't use Count(Distinct) on it. However, it's basically the same underlying expression.

Sum(
RangeMax(Aggr(
RangeSum(
Sum({< [Measure1]={"<1"} >} [Goal]),
-Sum(DISTINCT {< [Measure1]={"<1"} >} [IDs])
),
[Column1], [Column2], Column3, [Column4], Column5
),0))

As an aside, at least in this example, the aggr() isn't necessary since the dimensions in the table match the aggr() dimensions. Not sure if that's also the case in your actual app.