Consider the following MDX script:

WITH MEMBER [Measures].[Student Percent] As
iif (
([Measures].[Student Count],
Axis(1).Item(0).Item(Axis(1).item(0).Count-1).Dimension.CurrentMember.parent)
= 0,
Null, [Measures].[Student Count] / ([Measures].[Student Count],
Axis(1).Item(0).item(
Axis(1).item(0).Count-1
).Dimension.CurrentMember.parent)
) , FORMAT_STRING = '0.00%'
SELECT NON EMPTY
{{[Dim Gender].[Gender].[All],{[Dim
Gender].[Gender].[All].CHILDREN}}*{[Measures].[Student Count],
[Measures].[Student Percent]}} On Columns,
NON EMPTY {[Dim Teacher Qualification].[Dim Teacher
Qualification].[All],{[Dim Teacher Qualification].[Dim Teacher
Qualification].[All].CHILDREN}} On Rows
FROM [Correlates DW Model1 B]

Returns something like the following (ignore formatting).

All Female
Male
Count Percent Count Percent Count
Percent

All 114579 (null) 55743 (null) 58836 (null)
In-field 111107 96.97% 54206 97.24% 56901 96.71%
Out-field 4551 3.97% 2297 4.12% 2254 3.83%
N/A 58855 51.37% 28752 51.58% 30103 51.16%

In essence, the caluclated member [Student Percent] is giving me the
percent of the column total. I now need to also calculate the percent
of row total. I have tried to do it using similar approach (using the
axis function), but it does not work. Keep in mind that I need to keep
both the count and the percent. Can someone please help or point me in
the right direction?

Thanks,
Partha

Hi Partha,

Could you give a sample of what the query results should look like, when
calculating the percent of row total - should measures be on Axis(0) or
Axis(1), for example?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***