This article uses an example to explain how to calculate the percentage of the total in SSRS in a particular group or complete table using the Sum function and arithmetic operators.
To demonstrate the SSRS percentage of total calculation, we use the Employee table below, which has 15 records grouped by Occupation. The image below shows the records in the Table report.
Right-click on any field of the Details row, choose Insert a Row, and select the Outside Group—Below option to add a new row. Next, Merge the first three columns to show the difference between each group. For the SSRS percentage of total calculation, add a new column to the right side of the Income and Sales and name the headers GPer Inc and GPer Sl. Then, right-click the textbox under the GPer Sl and choose the Expression option. To understand the report, I suggest you refer to the Charts, Table, Grouping, and Format Table articles in SSRS.
Calculate the percentage of the total in SSRS
It opens the following expression window to calculate the percentage of the total. To get the same, we have to use the aggregate Sum function. The code below divides each individual sales by the total employee sales and multiplied by 100 to calculate the percentage of the total for each Occupation group. For more functions >> Click Here!
=(Fields!Sales.Value / Sum(Fields!Sales.Value, "Occupation")) * 100
Similarly, right-click the textbox under GPer Inc and write the below SSRS expression to calculate the percentage of the total of the YearlyIncome for each Occupation group.
=(Fields!YearlyIncome.Value/ Sum(Fields!YearlyIncome.Value, "Occupation")) * 100
To show you the difference in the actual result and the percentage, we will format the GPer Inc. To do so, right-click the textbox and choose the Text Box Properties option. Next, change the Number format from Default to Percentage and add two decimal places. It will add the percentage symbol for each value.
Please click the SSRS report preview tab to see the percentage of the total employee yearly income and sales for each occupation group. As you can see, GPer Sl shows the correct result without the %symbol. However, GPer Inc shows strange results. If you format the value to a percentage by default, it will multiply the existing value by 100.
Please edit the expression and remove the 100 to eliminate the extra multiplication.
=Fields!YearlyIncome.Value / Sum(Fields!YearlyIncome.Value, "Occupation")
The second option is using the FormatPercent function, which allows you to avoid the format number step. The SSRS FormatPercent expression below calculates the percentage of total sales for each occupation group.
=FormatPercent(Fields!Sales.Value / Sum(Fields!Sales.Value, "Occupation"), 2)
The report preview shows the correct results for calculating the percentage of total income and sales in the Occupation group.
SSRS Percentage of Total Calculation at Table level example
Let me create two more new columns and name TPer Inc and Var TPer Sl to calculate the percentage of the total for the whole table. To do so, you must replace the group name (“Occupation”) with the table name (“Tablix1”) or DataSet name (“DSet”). First, add the expression below for the textbox under the TPer Inc header. Next, format the text box number property to percentage.
=(Fields!YearlyIncome.Value / Sum(Fields!YearlyIncome.Value, "DSet"))
Similarly, write the following SSRS expression for the textbox under the GPer Sl header to find the percentage of the total table sales. Again, we used FormatPercent to avoid formatting the textbox number property.
=FormatPercent(Fields!Sales.Value / Sum(Fields!Sales.Value, "DSet"), 2)
The report below calculates the total income and sales percentage for each occupation group and includes the complete employee table.
The two SSRS expressions below calculate the total percentage for the complete group against the table.
=FormatPercent(Sum(Fields!YearlyIncome.Value, "Occupation") / Sum(Fields!YearlyIncome.Value, "DSet"), 2)
=FormatPercent(Sum(Fields!Sales.Value, "Occupation") / Sum(Fields!Sales.Value, "DSet"), 2)
The report preview shows the same.
