Answered by:
Report Builder 3.0  Matrix  Create Column with Percentages based on Totals Column
Question

I have created a Matrix in Report Builder 3.0 for a SharePoint list. It works great.
The matrix includes data grouped by months and grouped by the number of Yes and No answers to a single question.
Now I would like to add a column that calculates the percentage of the total based on the number of Yes answers.
The matrix looks like this:
Question Yes No Total
Jan 5 5 10
Feb 10 10 20
March 15 10 25
I have a added a new column title Percentage and have tried several different expressions and I keep getting errors.
I would like the matrix to look like this:
Question Yes No Total Percentage
Jan 5 5 10 50%
Feb 10 10 20 50%
March 15 10 25 60%
The percentage is the Number of Yes answers divided by the Total.
I have search online and cannot find the solution for us. I thought it would be simple but Report Builder is proving not to be so easy to figure out. I use Excel Pivot tables all the time and can easily create this in Excel.
Answers

Hi NikkiZ,
How do you add total column to the matrix? I think this is the same to add a new column besides the total column. Please take the following steps:
1. Right click the total column, select “Insert Column”
2. Select “Right”
If you see group column, please select "Outside GroupRight"
If you have any question, please feel free to ask.
Thanks,
Eileen
 Marked as answer by Eileen Zhao Saturday, July 30, 2011 2:56 PM

Hi Analyst Kim,
It seems that you would like to calculate the percentage of the total based on the number of Yes answers.
If that’s the case, I suggest you can use expression in Matrix to reach your goal, please refer to the following steps and then check if it works for you.
1. Add a new column besides the Total column, and name it as Percentage.
2. Rightclick the blank textbox and select Expression.
3. Type in the expression like following: =Sum(Iif(Fields!FiledsName.Value=”Yes”,Fields!Number.Value,0))/sum(Fields!Number.Value)
Note: please replace FieldsName and Number with your own fields name.If you have any question, please feel free to ask.
Thanks,
Eileen Marked as answer by Eileen Zhao Saturday, July 30, 2011 2:56 PM
All replies

Hi
I've just resolved a similar problem in Visual Studio whereby I had created a matrix with several columns and wanted to show each column as an overall percentage of each row.
It wasn't in sharepoint, but it may give you an idea of where to look to help you resolve this issue?
Basically, you have to tell the formula which set of data you want to add together to use as the number to do the divison by:
For example:
Column1 Column 2  Column 3 
Row 1  10  10  10 
Row 2  10  10  20 
If we assume we want to add the numbers in row 1 and show them as a percentage, we would expect to see 33% across all columns. In row 2, we would expect to see 25%, 25% and 50%. The formula to do this is:
Sum(Field)/Sum(Field,"RowGroupName")
It works in Visual Studio in a report matrix  hope it helps point you in the right direction for Sharepoint?
Regards
Kaz

Hi Analyst Kim,
It seems that you would like to calculate the percentage of the total based on the number of Yes answers.
If that’s the case, I suggest you can use expression in Matrix to reach your goal, please refer to the following steps and then check if it works for you.
1. Add a new column besides the Total column, and name it as Percentage.
2. Rightclick the blank textbox and select Expression.
3. Type in the expression like following: =Sum(Iif(Fields!FiledsName.Value=”Yes”,Fields!Number.Value,0))/sum(Fields!Number.Value)
Note: please replace FieldsName and Number with your own fields name.If you have any question, please feel free to ask.
Thanks,
Eileen Marked as answer by Eileen Zhao Saturday, July 30, 2011 2:56 PM


Hi NikkiZ,
How do you add total column to the matrix? I think this is the same to add a new column besides the total column. Please take the following steps:
1. Right click the total column, select “Insert Column”
2. Select “Right”
If you see group column, please select "Outside GroupRight"
If you have any question, please feel free to ask.
Thanks,
Eileen
 Marked as answer by Eileen Zhao Saturday, July 30, 2011 2:56 PM

hi
i use a tablix to genarate my report but i need one field to show percent of row data see example:
c1 p1 c2 p2 c3 p3
1 22 %22 35 %35 44 %43
a 12 %29 15 %36 14 %34
b 10 %16 20 %34 30 %50
2
a
b
c
3
a
How to create this report ? i created this without percentage col
 Edited by rmajidi Monday, January 6, 2014 6:13 AM

Hi Analyst Kim,
It seems that you would like to calculate the percentage of the total based on the number of Yes answers.
If that’s the case, I suggest you can use expression in Matrix to reach your goal, please refer to the following steps and then check if it works for you.
1. Add a new column besides the Total column, and name it as Percentage.
2. Rightclick the blank textbox and select Expression.
3. Type in the expression like following: =Sum(Iif(Fields!FiledsName.Value=”Yes”,Fields!Number.Value,0))/sum(Fields!Number.Value)
Note: please replace FieldsName and Number with your own fields name.If you have any question, please feel free to ask.
Thanks,
Eileen 