 Highlighted

# PIVOT Table - Insert Calculated Field

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.

Kumara B.
4 Replies
Highlighted

# Re: PIVOT Table - Insert Calculated Field

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

Highlighted

# Re: PIVOT Table - Insert Calculated Field

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))``
Highlighted

# Re: PIVOT Table - Insert Calculated Field

Edit: misread the the original post.

Highlighted

# Re: PIVOT Table - Insert Calculated Field

Result will be different on aggregation