Calculate percentage of total columns based on total column in SSRS matrix

Question

Looking at adding a column to my SSRS Matrix, which will give me a percentage of the total column in this row.

I use the following expression, but keep getting 100% for my percentages (I assume this is because the final value is evaluated last, so does it just do Total / Total?

=FORMAT((Fields!ID.Value/SUM(Fields!ID.Value)), "P") 

The field identifier is calculated in SQL, not in SSRS.

for example

 Site | Value 1 | %1 | Value2 | %2 | Total 1 | 20 | 50% | 20 | 50% | 40 

Answer 1

This is probably because you need to determine the correct area for the SUM function:

SUM(Fields!ID.Value,"group_name") instead of the usual SUM(Fields!ID.Value)

Updated:

It took me a while to make an example, since I did not have reporting services when I first answered you.

You can see the result and field values.

Answer 2

It is difficult to provide details without additional information about setting up your groups, but you should look at using the scope option for aggregate statements such as SUM or first:

 =SUM(Fields!ID.Value, "NameOfRowGrouping") / SUM(Fields!ID.Value, "TopLevelGroupName") 

In addition, in order to maintain purity, you must transfer your format from the expression to the placeholder or text box properties that contain your value.