3 Replies Latest reply on Apr 20, 2018 12:27 PM by Pradeep Repala

# Grand Totals and horizontal vs. vertical calculations (subtotals should account for the weight of the number in each cell)

Hello -

Hoping someone can help me with this. I have a problem with the "Actual Completes (%)" (3rd column), the subtotal is a simple average of cells for that category, I want it to be a weighted average.

****PLEASE DON'T CONSIDER THE NULL LINE******

For category 1, the subtotal of "Actual Completes (%)" computed as a simple average like the following: (82% + 55% + 63%) / 3 = 66 % ( I know it should be 67 but the numbers in the cell are round up), and it is not what I want....

For you to better understand, "Actual Completes (%)" = "Actual Completes (#)" / "Quota"  ---- (or 3rd column = 2nd column / 1st column)

My problem is that each cell has its own "dynamic" weight, but subtotals do not take it into account.

For example, for "category 1" and "Quebec"

for subcategory A 82% represent 129 completes / 158  ---- (then its weight when computing the average should be 158 of 271 (twich is the total of "Quota" for tha category)

for subcategory B 55% represent 49 completes / 89  ---- (then its weight when computing the average should be 89 of 271 wich is the total of "Quota" for tha category)

for subcategory C 63% represent 15 completes / 24  ---- (then its weight when computing the average should be 24 of 271 (wich is the total of "Quota" for tha category)

So what I would like to see in the total for "Actual Completes (%)" would be 82%*(158/271) + 55%*(89/271) + 63%*(24/271) = 71%

I tried to use Table Calculation (Table (across), Table (down), etc.) and could not figure it out. The subtotal is always a simple average of whatever is already in the column...

Need help

• ###### 1. Re: Grand Totals and horizontal vs. vertical calculations (subtotals should account for the weight of the number in each cell)

You may use a level of detail expresion like

SUM(completes) / {FIXED category:  SUM (quota)}

Hope that helps!

• ###### 2. Re: Grand Totals and horizontal vs. vertical calculations (subtotals should account for the weight of the number in each cell)

@luc,  I've got the  same issue, please let us know if you figured out a worked around

• ###### 3. Re: Grand Totals and horizontal vs. vertical calculations (subtotals should account for the weight of the number in each cell)

@ Luc Dumont, I played little and I think I achieved what you are looking for.

Attached sample file.