Average of ratios

[dafnis14]

Hi,

I'm a bit confused about the difference in results I get for total of ratios.

In the following table I have used

1.Sum of rows  (yellow)

2.Average of Rows (green)

3. Average of a ratio calculated in the script for each product group (orange)

My question is why is there a difference between sum of rows and average of rows?  Which is "preferable"?  

Thanks!

 

[PradeepReddy]

can you share the sample app...

[dafnis14]

Hi,
I attached to my post.

Thanks!

[mikevwang]

Sum of rows gets your total actual sales / total planned sales. This is giving you the weighted average of the ratios, with weights on each row being the % of total planned sales.

Average of rows is the unweighted average of the ratios.

Most of the time, people are expecting to see the total actual vs planned, so you would use sum of rows in that case. However you should decide based on what you are trying to show.

[dafnis14]

Hi Mike,

Thanks for your reply!

Not sure I understand ..

I used Expression Total (yellow)  and Average  (not Sum) of Rows  (green) .

is the Expression Total  considered a weighted average?

I was looking to find out what is the right way to present a total of  a ratio..

Thanks!

[mikevwang · Accepted solution]

Yes, the expression total is the weighted average. 

Here is an example to help you decide:

If you only have 2 BUs and one of them has $5M/$10M of actual vs planned sales and the other has $20k/10k of actual vs planned sales, then if you want the total ratio to be of the company, you should use the expression total (yellow). (5.02M/10.01M=50.1%) If you want the average ratio of all the BUs, then you use the average (green). (average(50%,200%)=150%).

When the total company sales is almost 50% behind plan, do you want the column total to show you are 50% ahead of plan? 

[dafnis14]

Got it!

Thanks!