Apr 07 2020 04:13 AM
Apr 07 2020 11:23 AM
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
Apr 07 2020 11:47 AM
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))
Apr 07 2020 12:58 PM - edited Apr 07 2020 04:06 PM
Edit: misread the the original post.
Apr 07 2020 01:28 PM
Result will be different on aggregation