Sum if greater than 0 else 0

[msshawn3019]

How to Sum DATA if greater than 0, else display $0

Basic algorithm:  If (Sum(DATA)>0, Sum(DATA), 0)

Using something like this:

Sum({<[CyclePeriod]=>}DATA), [ClientId],[ClientName],[Account],[CyclePeriod])

[vinieme12]

You already have the solution

if(Sum({<[CyclePeriod]=>}DATA)>0,Sum({<[CyclePeriod]=>}DATA),0)

Are you doing any aggregations using AGGR()

Can you provide more details such as dimensions used in chart and copy paste your actual expression

[msshawn3019]

I think I need to do aggregations in the solution - by [ClientId],[ClientName],[Account],[CyclePeriod]

As I have multiple clients, accounts and Month/Year periods for my pivot table

[msshawn3019]

Something like this but it's not giving a 0 when there is no value (DATA) for a given period, client, account:

=If (sum(aggr(sum({<[CyclePeriod]=>}DATA),[ClientId],[ClientName],[Account],[CyclePeriod])) > 0,
sum(aggr(sum({<[CyclePeriod]=>}DATA),[ClientId],[ClientName],[Account],[CyclePeriod])), 0)

[msshawn3019 · Accepted solution]

I ended up doing it like thig:

If (Sum( {< [Billing Cycle]={">=$(=Max([Billing Cycle])-2)<=$(=Max([Billing Cycle]))"} >} DATA) <>0, Sum( {< [Billing Cycle]={">=$(=Max([Billing Cycle])-2)<=$(=Max([Billing Cycle]))"} >} DATA)