Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

How to create a weighted average Moody's rating? Expand / Collapse
Posted Monday, April 29, 2013 2:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 9:32 AM
Points: 5, Visits: 76
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?
Post #1447764
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse