Altair® Panopticon

 

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%