Hello OLAP List,

I am new to AS, OLAP and MDX and would really appreciate a bit of help
with percent-to-total calculated measures. I have read through many of
the list samples and tried some that seem applicable, but I still have
not been able to solve my problem. I will do my best to describe my
scenario, so if you have any questions or my syntax is incorrect please
let me know.

I have a snowflaked dimension called Attributed Class that details
levels of granularity about responsibility and failure modes for
"Returns". The Attributed Class Dimension is as follows:

Attributed Class:
+ Attributed Class
++ Z Code
++ XYZ Code

Also, I have a measure called "Returns Last Wk". What I want to
generate is 2 separate reports (in Reporting Services) that handle the
dimensionality of the denominator in the calculated "Percent Returns
Last Wk" differently based on the situation. Here are the desired
reports:

Report 1 - Returns by Attributed Class

Attributed Class | Returns Last Wk | Percent Returns Last Wk
-------------------------------------------------------------
US* | 150 | 75.00%
Customer | 30 | 15.00%
Process | 12 | 6.00%
No Defect | 8 | 4.00%
Total | 200 | 100.00%

*the use of US throughout this thread refers to "our" fault. It does
not refer to the United States (US) geography.

Report 2 - Returns by Z,XYZ Code (US)

Z Code | XYZ Code | Returns Last Wk | Percent Returns Last Wk
--------------------------------------------------------------------
+Z1 | | 100 | 66.66%
+Z2 | | 32 | 21.33%
-Z3 | | 18 | 12.00%

So, I have the first report completed. MDX:

select
{[Measures].[Returns Last Wk], [Measures].[Percent Returns Last Wk]}
on columns,
non empty {order({[Attributed Class].children}, ([Measures].[Returns
Last Wk]), DESC)} on rows
from
[MyCube]
where
([Product].[All Product].[Gizmos],
[Source].[All Source].[valid])

and the calculated measure is easy:

[Measures].[Returns Last Wk]/([Measures].[Returns Last Wk],[Attributed
Class].[All Attributed Class])

The second report is what is stumping me. I was hoping to be able to
use a single calculated measure include in both reports, but at this
point I would be happy with any solution that gets me going again.
Currently, if I use the same calculated member the denominator is
[Attributed Class].[All Attributed Class] and not just a particular
Attributed Class, [Attributed Class].[All Attributed Class].[US], for
example:

Z Code | XYZ Code | Returns Last Wk | Percent Returns Last Wk
--------------------------------------------------------------------
+Z1 | | 100 | 50.00%
+Z2 | | 32 | 16.00%
-Z3 | | 18 | 9.00%

This report should Total to 100.00% not the US contribution of 75.00%
from Report 1. If it makes a difference, this is going in to Reporting
Services, so the dataset will be rendered as a flattened recordset
format.

Cheers!

Hi,

try to define the denominator by using somthing like "[Attributed
Class].currentmember.parent".
Maybe this points to the right element!

cheers

"mdxwhip" <keehan.mallon (AT) wdc (DOT) com> schrieb im Newsbeitrag
news:1114735477.294122.4070 (AT) f14g2000cwb (DOT) googlegroups.com...

Thanks for the input Mathias.

I had already tried [Attributed Class].currentmember.parent as the
denominator dimensonality prior to my original posting. It worked fine
for Report 1, but not for Report 2. In Report 2, the Percent to Total
at the Z Code level is fine, but 2 other problems arose:

1) The XYZ Code drill down was then based on 100.00% of the Z Code
member and
2) The Total for Report 2 reflects the % that was attributed to US of
ALL Attributed Classes, 75.00%, not the % that was attributed to US of
only the US Attributed Class, should be 100%:

Z Code | XYZ Code | Returns Last Wk | Percent Returns Last Wk
--------------------------------------------------------------------
+Z1 | | 100 | 66.66%
+Z2 | | 32 | 21.33%
-Z3 | | 18 | 12.00%

What I am trying to get for Report 2 is:

Z Code | XYZ Code | Returns Last Wk | Percent Returns Last Wk
------------------------------*------------------------------*--------

+Z1 | | 100 | 66.66%
+Z2 | | 32 | 21.33%
-Z3 | | 18 | 12.00%

Any other ideas for me to deal with my Report 2 troubles?

To close the issue,I solved the problem by using 2 measures. It turns
out that I needed 2 measures, one for Report 1 and one for Report 2, to
perform this task as the measure would have required different results
at the same level in the different reports. Anyway, for Report 1 I
used:

[Measures].[Returns Last Week]/([Measures].[Returns Last
Week],[Attributed Class].currentmember.parent)

as Mathias and I discussed above. For Report 2, I ended up using the
Iif() statement to catch the level and specify the dimensionality of
the measure for that level. Anyway, here is what it ended up looking
like:

Iif([Attributed Class].currentmember.level is [Attributed
Class].[Attributed Class],
[Measures].[Returns Last Week]/([Measures].[Returns Last
Week],[Attributed Class].currentmember),
Iif([Attributed Class].currentmember.level is [Attributed
Class].[Final Z Code],
[Measures].[Returns Last Week]/([Measures].[Returns Last
Week],Ancestor([Attributed Class].currentmember,1)),
Iif([Attributed Class].currentmember.level is [Attributed
Class].[Final XYZ Code],
[Measures].[Returns Last Week]/([Measures].[Returns Last
Week],Ancestor([Attributed Class].currentmember,2)), -1)))