0
  C1  C2  C3 C4 C5  C6 C7 C8    Total   **Percentages**

======================================================

R1  6   1   8  8   2   1  1 0   27  **60%**
R2  0   0   0  5   1   1  0 0   7   **16%**
R3  2   0   3  2   0   1  0 0   8   **18%**
R4  2   0   0  1   0   0  0 0   3   **7%**
TTL10  1   11 16   3   3  1 0   45  **100%**

How to calculate the individual row percentages in SSRS

Thank you.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
Swetha G
  • 1
  • 1

3 Answers3

1

If you're not filtering your dataset, you could use the Dataset sum to get the overall total and use that as the denominator in your expression.

If your table is a matrix with the C1 - C8 all coming from one field, then your formula would just be:

=Sum(Fields!YourField.Value) / Sum(Fields!YourField.Value, "Dataset1")

If the C1 - C8 fields are in separate fields, you can use the same expression used for your total column as the numerator and then divide by the SUM of all the other fields.

=Sum(Fields!C1.Value + Fields!C2.Value + Fields!C3.Value + Fields!C4.Value + Fields!C5.Value + Fields!C6.Value + Fields!C7.Value + Fields!C8.Value) 
/ 
Sum(Fields!C1.Value + Fields!C2.Value + Fields!C3.Value + Fields!C4.Value + Fields!C5.Value + Fields!C6.Value + Fields!C7.Value + Fields!C8.Value, "Dataset1"))
Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
0

I will work on SQL rather on SSRS. Here is my approach. For SSRS here is the link. 

DECLARE @YourTable TABLE
(
Col INT
,Col1 INT
,Col2 INT
,Col3 INT

)

INSERT INTO @YourTable VALUES
(1 , 20, 10, 15)
,(2 , 30, 12, 14)
,(2 , 22, 2, 4)
,(3 , 3, 10, 15)
,(5 , 5, 14, 14)
,(2 , 21, 32, 4)
SELECT * FROM @YourTable


; WITH CTE AS

(SELECT *,Col+Col1+Col2+Col3 AS SumCol FROM @YourTable)

SELECT *, CAST(SumCol*100.0 / SUM(SumCol) OVER()  as DECIMAL(28,2)) FROM CTE
Community
  • 1
  • 1
Hari
  • 192
  • 2
  • 12
0

Here's another approach:

  1. Create a row outside of the details group, above the first row of data.
  2. Populate a Textbox in the new row =Sum(Fields!Total.Value). Rename the Textbox something unique, such as Denominator.
  3. Hide the row.

  4. For your percentage formula in the details row, use something like:

    =Sum(Fields!Total.Value) / ReportItems!Denominator.Value

Adam Sheppard
  • 11
  • 1