I've gotten pretty far with this, but I'm falling one step short...
The basic data in my cube pertains to holdings of financial instruments in portfolios. An "asset" would be a security, and these securities are held in one or more portfolios. Each security is rated by Moody's, and these ratings are in alpha-numeric buckets. Examples of the possible ratings would be: Aa3, Aa2, Aa1, Baa3, Baa2, Baa1, Ba3, Ba2, Ba1, Caa3, Caa2, etc.
I'd like to create a calculated measure that is the weighted average Rating. (The weighting factor is the dollar Amount of each holding).
I've created a table that assigns each rating a numeric equivalent. In my fact table I've created a field (wgt Rating) that multiplies the Amount of the holding by the rating's numeric equivalent, and I've added this field as a measure in the cube, with the visibility set to False. Then I created a calculated measure that divides the wgt Rating by the Amount of the holding. This effectively gives me a weighted average Rating, but it is numeric. To get the alpha Rating, I have to go back to the table and interpolate.
Can anyone advise me on how to get SSAS to take that last step and return the alpha Rating?