January 28, 2015 at 2:31 pm
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
March 19, 2015 at 11:46 am
Not sure if you still need help with this. If you do, could you please post your query?
March 19, 2015 at 1:49 pm
Thanks. I actually just solved it this week using help from Chris Webb's blog:
March 19, 2015 at 2:08 pm
TWH (3/19/2015)
Thanks. I actually just solved it this week using help from Chris Webb's blog:
Perfect, I would have pointed you in that direction as well.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply