Forum Discussion

kumarballa's avatar
kumarballa
Copper Contributor
Apr 07, 2020

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.

Could you please advise what would be the reason.

Thanks in advance.
Kumara B.
Formulas and Functions

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 07, 2020

    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))
  • ExcelExciting's avatar
    ExcelExciting
    MVP
    Apr 07, 2020

    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

Resources