|
Ratio of Sums
The comparison between the sum of a selected measure divided by the sum of the selected reference measure.
The formula:
Ratio of Sums = sum(selected measure) / sum(selected reference measure)
Sample
Given the sample data:
|
Region |
Store |
Actual |
Target |
|
North |
A |
$1,300 |
$2,000 |
|
North |
B |
$750 |
$1,000 |
|
North |
C |
$2,100 |
$3,000 |
|
South |
D |
$4,700 |
$4,000 |
|
South |
E |
$2,000 |
$2,000 |
Sample fields
Creating a Table visualization with Breakdowns Region and Store with subtotals and grand totals produces:
|
Region |
Store |
Actual |
Target |
|
North
|
A |
$1,300 |
$2,000 |
|
B |
$750 |
$1,000 |
|
|
C |
$2,100 |
$3,000 |
|
|
North Total |
$4,150 |
$6,000 |
|
|
South
|
D |
$4,700 |
$4,000 |
|
E |
$2,000 |
$2,000 |
|
|
South Total |
|
$6,700 |
$6,000 |
|
Grand Total |
|
$10,850 |
$12,000 |
Setting the Column to Actual and the Reference Column to Target with the format set to 0.00% results to the following Ratio of Sums values:
|
Region |
Store |
Actual |
Target |
Ratio of Sums |
|
North
|
A |
$1,300 |
$2,000 |
65.00% |
|
B |
$750 |
$1,000 |
75.00% |
|
|
C |
$2,100 |
$3,000 |
70.00% |
|
|
North Total |
$4,150 |
$6,000 |
69.17% |
|
|
South
|
D |
$4,700 |
$4,000 |
117.50% |
|
E |
$2,000 |
$2,000 |
100.00% |
|
|
South Total |
|
$6,700 |
$6,000 |
111.67% |
|
Grand Total |
|
$10,850 |
$12,000 |
90.42% |
The results per row
Computation details:
|
North A: $1,300 / $2,000 = 65.00% |
South D: $4,700 / $4,000 = 117.50% |
|
North B: $750 / $1,000 = 75.00% |
South E: $2,000 / $2,000 = 100.00% |
|
North C: $2,100 / $3,000 = 70.00% |
South Total: $6,700 / $6,000 = 111.67% |
|
North Total: $4,150 / $6,000 = 69.17% |
Grand Total: $10,850 / $12,000 = 90.42% |
Collapsing the North region results to the following Ratio of Sums values:
|
Region |
Store |
Actual |
Target |
Ratio of Sums |
|
North Total |
$4,150 |
$6,000 |
69.17% |
|
|
South
|
D |
$4,700 |
$4,000 |
117.50% |
|
E |
$2,000 |
$2,000 |
100.00% |
|
|
South Total |
$6,700 |
$6,000 |
111.67% |
|
|
Grand Total |
$10,850 |
$12,000 |
90.42% |
|
The results per row
The rest of the computation details are the same except for the collapsed North region:
North = ($1,300 + $750 + $2,100) / ($2,000 + $1,000 + $3,000) = 69.17%
Or
North = $4,150 / $6,000 = 69.17%
Collapsing the South region results to the following Ratio values:
|
Region |
Actual |
Target |
Ratio of Sums |
|
North |
$4,150 |
$6,000 |
69.17% |
|
South |
$6,700 |
$6,000 |
111.67% |
|
Grand Total |
$10,850 |
$12,000 |
90.42% |
The results per row
The computation details for the collapsed South region:
South = ($4,700 + $2,000) / ($4,000 + $2,000) = 111.67%
Or
South = $6,700 / $6,000 = 111.67%