SSRS Switch function showing error for simple math

Question

This is my data . I have to calculate the value of Banana by subtracting 1-2-3. I want to do it in the report because I feel it will be easier in the report.

I have a basic report with no groupings.

For the totals, I do the following calculation:
= SWITCH(
Fields!Order.Value = 6,

   Sum(IIF( Fields!Order.Value = 1 ,CDBL(Fields!Totals.Value),CDBL(0)))  
			- Sum(IIF( Fields!Ordering.Value = 2 ,CDBL(Fields!Totals.Value),CDBL(0)))  
			- Sum(IIF( Fields!Ordering.Value = 3 ,CDBL(Fields!Totals.Value),CDBL(0)))  
			,  
	1 = 1,Sum(Fields!Totals.Value)  
	)  

The issue now is that when I use the expression above, it shows #error for Banana rather than the answer. How can I display the numbers properly?

Answer 1

Hi @summoningknoweldge ,

Please try the following expression:

=SUM(Switch(Fields!Order.Value=1,Fields!Total.Value)-Switch(Fields!Order.Value=2,Fields!Total.Value)-Switch(Fields!Order.Value=3,Fields!Total.Value))  

Design:

Preview:

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.