Skip to Content
0
Nov 23, 2009 at 06:53 AM

How to calculate weighted average?

1242 Views

Hi Gurus,

I have a requirement that I don't know how to implement it using BI Query Designer.

The requirement is this: there are two key figures, Cost and Quantity.

While users key in time interval runtime, there should be a calculated key figure that shows the weighted average.

For example, User keys in 2009.01 ~ 2009.08.

The weighted average should be calculated like this:

(C1 means Cost in 2009/01;C2 means Cost in 2009/02,Q1 means Quantity in 2009/01, Q2 means Quantity in 2009/02..etc)

Weighted Average = (C1*Q1 + C2*Q2 + .....+ C8*Q8)/(Q1+Q2+Q3+...+Q8)

Moreover, if users put "company code" in reports, the weighted average should be calculated like this:

(C1A means Cost in 2009/01 for Company A; C1B means Cost in 2009/01 for Company B; Q1A means Quantity in 2009/01 for Company A; Q1B means Quantity in 2009/01 for Company B)

Weighted Average = (C1A*Q1A + C1B*Q1B + ... + C8B*Q8B)/(Q1A+Q1B+...+Q8A+Q8B)

I can't figure a good way to implement this requirement.

Can somebody help me?

Thank you.