# Sub Total only when All prices have value in Matrix Tablix • ### Question

• Hi,

I am trying to subtotal the MTRC_INV_P as shown below. But I need to apply some rules while summing them all. 1. ADD only when CODE = 0 and display at bottom.

This rule is done by using the simple iff condition: =SUM(IIf(Fields!CODE.Value = 0,Fields!MTRC_INV_P.Value,0))

2. Don not add the total when any one of the MTRC_INV_P has a value of 0.0 or Null.

I am not sure how to include the second rule with the 1st one.

Any help is highly appreciated.

Thanks.

Wednesday, May 8, 2013 9:40 AM

• The below expression worked for me. However I need to test little more. :)

=SUM(IIF(Sum(IIF(IsNothing(Fields!MTRC_INV_P.Value) AND (Fields!CODE.Value = 0 OR Fields!CODE.Value = 1),1,0)) >0,0,IIf(Fields!CODE.Value = 0,Fields!MTRC_INV_P.Value,0)))

• Proposed as answer by Thursday, May 9, 2013 10:00 AM
• Marked as answer by Monday, May 13, 2013 9:38 AM
Thursday, May 9, 2013 7:59 AM

### All replies

• Hi Rikz,

Try this expression:

`=SUM(IIf(Fields!CODE.Value = 0 AND ISNUMERIC(Fields!MTRC_INV_P.Value) = TRUE,Fields!MTRC_INV_P.Value,0))`

Regards Harsh

Wednesday, May 8, 2013 11:06 AM
• Thanks Harsh.

But it din't work out and may be i couldn't explain the problem. I will try to explain it properly. The color coding as shown below is handled with a CODE value if white (0) then add if gray(1) then do not add in the total (neglect the black (2) ).

Thus the total for T1 is 100 +100 = 200

The next thing is if any of the cells with white background doesn't have price value then do not go for summation for that column and display 0. make sense?? :( m totally blank on the 2nd part.

Wednesday, May 8, 2013 1:18 PM
• Just found out InScope function but not sure if I have understood it properly.

Can we use InScope in this scenario?

Thanks

Thursday, May 9, 2013 5:00 AM
• The below expression worked for me. However I need to test little more. :)

=SUM(IIF(Sum(IIF(IsNothing(Fields!MTRC_INV_P.Value) AND (Fields!CODE.Value = 0 OR Fields!CODE.Value = 1),1,0)) >0,0,IIf(Fields!CODE.Value = 0,Fields!MTRC_INV_P.Value,0)))

• Proposed as answer by Thursday, May 9, 2013 10:00 AM
• Marked as answer by Monday, May 13, 2013 9:38 AM
Thursday, May 9, 2013 7:59 AM