Adding Calculations to Matrix total

Question

This is killing me. Any help on this would be greatly appreciated. How would I add some calculated fields to a group in a Matrix? For example I would like to add Yield Ratio which would be the # Binds/# Submissions. Hit ratio would be # Binds /# Quotes

Answer 1

Hi @Ted Best ,
I tested it locally. I think the method worth trying is to use TSQL to complete the calculation.
Here are the steps :
1.I created a Tablix named T1 in SSMS. As shown in your image.

2.Execute the following statement when creating the Dataset:

select * from T1  
union  
select a.YearMonth,'Yield Ratio' Type,cast(a.Count as float)/cast(b.count as float) count,5  
from (select * from T1  
where Type='Binds') a  
inner join (select * from T1  
where Type='Submission') b  
on a.YearMonth=b.YearMonth  
union  
select a.YearMonth,'Hit ratio' Type,cast(a.Count as float)/cast(b.count as float) count,6  
from (select * from T1  
where Type='Binds') a  
inner join (select * from T1  
where Type='Quoted') b  
on a.YearMonth=b.YearMonth  

3.Create a matrix and sort by "SortOrder", as shown in the figure below:
Design:

Preview:

Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.

Answer 2

Thank you so much Joy! That was very helpful!

I was wondering if you could help me with one other thing

The yellow highlighted field (Average TWP) is a calculated field using values in the New Business, Renewals & Total WP group. I tried the following calculated field but it doesn't work. I'm assuming because I'm looking for a value in a different group. Is there a way to pull that value from a different group?

Also is there a way to add a blank line in between one more more of the groups but not all in a Matrix? (Green arrow)

=Switch(Fields!Section.Value = "New Business",VAL(REPLACE(Sum(IIF(Fields!Type.Value = "Declines/Not Quoted", Fields!Value.Value, 0))/Sum(IIF(Fields!Type.Value = "Submission", Fields!Value.Value, 0)),"NaN",0)),  
    Fields!Section.Value = "Renewals",VAL(REPLACE(Sum(IIF(Fields!Type.Value = "Renewals Declined", Fields!Value.Value, 0))/Sum(IIF(Fields!Type.Value = "Renewals Expiring", Fields!Value.Value, 0)),"NaN",0)),  
    ***Fields!Section.Value = "Total WP",Sum(IIF(Fields!Type.Value = "CY TWP", Fields!Value.Value, 0))/(Sum(IIF(Fields!Type.Value = "Binds", Fields!Value.Value, 0))+Sum(IIF(Fields!Type.Value = "Renewals Bound", Fields!Value.Value, 0)))***)