How to create a calculated measure that will show the Percent of All for any dimension

I want to have a generic PercentOfAll measure that can be used for any dimension of the cube. This PercentOfAll should act like Count, but instead of showing the number of rows in a cell, it will show the percentage  (100*number of rows for that member/all rows) .It should not matter what dimension is being viewed. Ex

There are a total of 100 rows in the source class.

For dimension columnA which returns value in  ColumnA column in source class, 10 rows contain "A" the rest contain "null". When viewed on the analyzer, with columnA as a row and PercentOfAll as a measure, the cells values should be:

"A"       10%

"null"  90%

 I can achieve this using an expression that would be specific to columnA dimension Ex columnA. CurrentMember/columnA.[All columnA].%All

But I do not want to have to define meausure for each and every dimension. I just want to have a measure that will be used for all dimensions. Ex 

For dimension columnB which returns value in  ColumnB column in source class, 50 rows contain "B" the rest contain "null". When viewed on the analyzer, with columnB as a row and PercentOfAll as a measure, the cells values should be:

"B"       50%

"null"  50%

  • 0
  • 0
  • 93
  • 0
  • 1

Respuestas

Never mind! I found it, or at least a version of it,  in the documentation

Calculated Member (Measure)

Value Expression = Measures.[%Count]/%MDX("Select from myCube")

Format String = ###.##%