SSRS - add percentage of total?

0
votes

I am just starting out in SSRS and have a dataset that looks like the below.

enter image description here

I have built this in a matrix table like so

enter image description here

I want to show what percentage each of the rows total is of the grand total, so for the attached image I would want to show what percentage 35 (counselling calls) is of 47 (total) and what percentage Legal calls (12) is of total (47)

I have tried =Fields!Calls.Value/sum(Fields!Calls.Value) but that just gives me 100% for both?

Would appreciate any help

1
reporting-servicesdaxssrs-2012

1 Answers

1
votes

You need to specify the scope of your SUM() expressions.

I'll briefly explain how scopes work and then get to the answer.

SSRS always evaluates an expression based on it's scope which is usually defined by the physical location of the expression within table/matrix. As you can see from your design all 4 textboxes show the same expression, [SUM(Calls}] which in fact is actually =SUM(Fields!Calls.Value). However they give different results because the scope of each is different. The first is in the category rowgroup and the month column group for example, the second one is in the category row group but in the total month group, and so on...

OK, now to the answer !

Assumming

  1. your rowgroup is called 'CategoryGroup` (you will see the name in the rowgroup panel under the main designer)
  2. your dataset is called DataSet1

You expression should be

=SUM(Fields!Calls.Value, "CategoryGroup") / SUM(Fields!Calls.Value, "DataSet1")

This basically reads..

Take the sum of the call column for all rows that are within the current CategoryRowgroup and divide by the sum of the call column across the whole dataset.

Notes

  • The scope names must be enclosed in quote and are case sensitive
  • The scope names must match either a row or column group, or a dataset name exactly.
  • the , "CategoryGroup" scope argument can probably be omitted as this should be the scope of the textbox anyway.