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.