Newbie in need of assistance with Calculated Measures/Growth Rate
I am still trying to figure that Calculated Measure stuff and I hope to learn from you. I have several cubes I am trying to create calculated measures using the Calculated Member Expression.
I have a cube called Cards. There is a measure called count that give total cards count of 15,000. There are two dimensions I am considering. One is the CreditGrade (A, B, C, D, nograde) and the other is the date of the scoredate.(2000-2015).
The CreditGrade dimension has two hierarchies. H1 being the creditnumber and H2 being the CreditGrade (The one I need).
The scoredate dimension has the scoreYear, ScoreQuarter, ScoreMonth, ScoreWeek.
Without adding any filters, there are 15,000 cards. When I add filters such as ClosedCards, ChargedoffCards, the total number of cards drops to 12,500.
1)I try to calculate the percentage of each individual grade based on the 12,500 and not the 15,000 (When I used [Measures].[%Count]/%MDX("Select From [Cards]"), I get the percentage based on the 15,000. But I would like the percentage based on the 12,500 which is what I am interested in.
2) On the columns, I have the ScoreDate (2000....2015) and the rows I have the ScoreGrade of A, B, C, D. as follows:
Scoreyear A B C D
2000 200 150 85 15
2001 275 300 25 25
and so forth all the way to 2015
I want to create a measure (growth rate) for each ScoreGrade throughout the years. For example, growth rate of A from 2000 to 2001, 2001 to 2002, 2002 to 2003 all the way to 2014 to 2015. From the table above, it should be (150-200)/200.; (85-150)/150; (15-85)/85 and so on. I would like this formula to be applied to the entire 12,500 and not the 15,000 so that each grade will show its growth rates over the years.
I am not sure how to go about it. I read the documentation, tried to apply it but it did not work. I got NA.
The MDX Query is as follows:
SELECT NON EMPTY [scoreGrade].[H2].[Scoregrade].Customers ON 0, NON EMPTY [ScoreDate].[H1].[ScoreYear].Customers ON 1 FROM [CARDS] %FILTER[charge-off].%NOY,[Closed].%NOT
The MDX Query for the ScoreYear is: SELECT NON EMPTY [ScoreDate].[H1].[ScoreYear].Customers ON 1 FROM [CARDS]
The MDX Query for the ScoreGrade is: SELECT NON EMPTY [ScoreGrade].[H2].[ScoreGrade].Customers ON 0 FROM [CARDS].
Thank you for your help. This will a start for me.