SSAS -> MDX -> Creating a column percentage within my query based on counts

Question

SELECT NON EMPTY {[Measures].[Fact Order Count]} ON COLUMNS, { ([Front Manager].[Front Manager Id].[Front Manager Id].ALLMEMBERS * [Order Type].[Order Type].[Order Type].ALLMEMBERS ) } ON ROWS FROM [TEST_DW] CELL PROPERTIES VALUE

So, I have three columns in the output. :

Front Manager, Order Type, Order Count

The above query shows me the counts for each manager and order type combination. I need a fourth column which would be a percentage of the types of orders for each front manager.

So, if there are four types of orders (A, B, C, D), and a manager had 25 of each order totaling 100. The fourth column would read 25%.....

I have scoured the web on how to do this, but have really come up short on this one. Any direction on this would be greatly appreciated, I am definitely new to MDX. Thanks.

ic3 ic3 · Accepted Answer · 2012-01-14T10:43:09

What you're looking for are MDX Calculated members.

Let's assume the member for order A is called : [Order Type].[Order Type].[Order A] and we want to calculate the percentage from the total.

WITH 
   MEMBER [Order A] AS ([Order Type].[Order Type].[Order A],[Measures].[Fact Order Count]) / ([Measures].[Fact Order Count]) , FORMAT_STRING = 'Percent'
SELECT
   {[Measures].[Fact Order Count],[Measures].[Order A]} on 0
...

What is important in the calculated members is that you can evaluate any MDX tuple (e.g ([Order Type].[Order Type].[Order A],[Measures].[Fact Order Count]) ). This changing if needed the values coming from the pivot axis (defined in on 0 and on 1..). Note you can add calculated members for the measures as well as the other dimensions.

SSAS -> MDX -> Creating a column percentage within my query based on counts

1 Answers