SSRS - Percentage Totals Row

0
votes

I am trying to build an SSRS report that has a column based on a percentage:

AgentSales   AgentCommission       Commission
20           .25                   5 
33           .12                   3.96
46           .76                   34.96
Totals:       99                   [Unknown Calculation]

However, where I am having problems is I need to get what would be the totals column? How would I calculate the Commission so that the number shown in [Unknown Calculation] to display what I assume would be the average commission?

Commission column is simply AgentSales * AgentCommission. The total commission wouldn't be a SUM(Commission), but would more or less be the average commission for all agents.

1
reporting-servicesssrs-2012
Hi. In this particular example, what would be formula for [Unknown Calculation], or average commission? Row 1 commission is 25, row 2 is 15.68 and row 3 is 1,222.2, so is it safe to say [Unknown calculation] value is (25 + 15.68 + 1,222.2) / 3 ? – Marko Radivojević
My apologies, but Row1 commission is 5 (from 20 * .25). The commission is simply AgentSales * AgentCommission. What I put in parenthesis is the formula used to get the Commission. I guess I should have been more clear in my original question – A M C
Well, actually it was clear. My mistake, I should have thought a little bit. – Marko Radivojević

1 Answers

2
votes

Assuming you want to calculate the weighted average commission then I would calculate it like this..

enter image description here

Expression 1: is simple as per your sample.

=Fields!AgentSales.Value * Fields!AgentCommission.Value

Expression 2: is the sum of the results of expression 1

=SUM(Fields!AgentSales.Value * Fields!AgentCommission.Value)

Expression 3: is the expression 2 divided by the sum of agent sales

=(SUM(Fields!AgentSales.Value * Fields!AgentCommission.Value))
 /
 SUM(Fields!AgentSales.Value)

The other cells should be self explanatory...

When we run the report we get the following.

enter image description here

If this is not what you wanted, then please edit your question to show the desired result and how you arrived at that result.

EDIT AFTER UPDATE FROM OP

If you want the cell marked "2" in my sample to be an average of the values above then simpley change it to

=AVG(Fields!AgentSales.Value * Fields!AgentCommission.Value)