Pivot table

[Carlaf_93]

Good morning,

 i have  a problem with the pivot table.

If i define a measure with a 'if formula' the total amount of that area is by default zero.

For example i have a dimension with agents and with products and i'm analyzing the sales. 

the formula is:

IF(SUM({<PRODUCT={'B'}>}SALES) <0,'0', SUM(SALES))

With this the single value is right but the total is by default zero.

What can i do?

[sunny_talwar]

Try using Sum(Aggr())

Sum(Aggr(
  If(Sum({<PRODUCT = {'B'}>} SALES) < 0, 0, Sum(SALES))
, Agent, Products))

[Carlaf_93]

Thanks, 

but this doesn't work.

[PrashantSangle]

can you show us what are you doing?? some more snip of dashboard will help alot for better solution

[Carlaf_93]

For example, i have a pivot table with those fields.

 i have a measure like --> if(sum({<FieldC={"DA"}>}value)<0, '0', sum(value))

(DA is an element of fieldC)

if i have all the pivot 'open' it works perfectly... when i close it some sections that contains the negative one gives as total 0.. whereas i wat the sum of the oters value and plus the zero value identified by the measure..... 

[Carlaf_93]

For example, i have those fields in a pivot table.

and a measure like --> if(sum({<Valuec={"ADA"}>}value)<0, '0', sum(value))

if i open all the fields in the pivot it works correctly. when i close some sections the totals are wrong because  it contains the single element that results zero and doesn't give me the total as the sum of the rest plus the zero's value.