MDX for weighted average?

Question

Post reply

MDX for weighted average?

Tom_Hogan

Hall of Fame

Points: 3894
More actions
January 28, 2015 at 2:31 pm

#150322

Hi,
I'm looking for some help writing some MDX for a weighted average. I'm relatively new to MDX so all help is appreciated. Some example data (Quality Level is Pass / Inspect ):
Account Volume Inspect Pass FailQuality Level
Account 1100 4 3175.00%
Account 27 1 10100.00%
Account 3231 9 90100.00%
Account 4303 12 11191.67%
Account 511 0 00NA
Our weighted average is defined as ( Volume * Quality Level ) / Volumel. We need to exclude any rows where there are no inspections. So for the above data the Weighted Quality Level would be defined as:
(A1V * A1QL) + (A2V * A2QL) + (A3V * A3QL) + (A3V * A3QL) / A1V + A2V + A3V + A42V
(100 * .75) + (7 * 1) + (231 * 1) + (303 * .9167) / 100 + 7 + 231 + 303
= 92.16%
My biggest issue is filtering out Account 5 so the number calculates correctly.
Thanks.
-Tom

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19126 More actions · Answer 1

Not sure if you still need help with this. If you do, could you please post your query?

Tom_Hogan Hall of Fame Points: 3894 More actions · Answer 2

Thanks. I actually just solved it this week using help from Chris Webb's blog:

https://cwebbbi.wordpress.com/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/

Martin Schoombee SSCoach Points: 19126 More actions · Answer 3

TWH (3/19/2015)
Thanks. I actually just solved it this week using help from Chris Webb's blog:
https://cwebbbi.wordpress.com/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/

Perfect, I would have pointed you in that direction as well.