PIVOT Table - Insert Calculated Field

[kumarballa]

Hello, Good day,
Following is my problem. I have inserted a formula in PIVOT table - Insert Calculated Field "=IF(Amount>0, Amount,0)". I am expecting to get all positive values in column "Amount" in the calculated field. After inserting this formula, it has worked perfectly except it is not showing Grand total for this field. I tried this on dummy data and it shows Grand total. But Grand total not working in above said data.

Could you please advise what would be the reason.

Thanks in advance.
Kumara B.

[#2]

hi @kumarballa 

please attached the sample file to advise your the correct solution 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

[#3]

@kumarballa 

for calculated fields

1) individual amount of other fields are aggregated and then the calculation is performed on result.

let say we have two amounts for the Shop1: -15 and 14 and would like to sum. First will be =-15+14=-1 and after that calculation of the field will be applied, i.e. result is zero.

2) On Grand Total are performed the same calculations as on other fields.

If you mean for above it shall be -15+14=0+14=14, you may select Add data to data model creating the Pivot table and add DAX measure like

Sum Ignoring Negatives:=SUMX(Range,IF(Range[Amount]>0,Range[Amount],0))

[#4]

Edit: misread the the original post.

[#5]

@Patrick2788 

Result will be different on aggregation