MDX percentage calculation

Question

I am trying to caculate the percentage and add it to my cube measure.

Basicaly i am trying to calculate the percentage of sales Week 01 to 02 Waiting Times 03 to 04 Waiting Times | | 99 to 100 Waiting Times

The formula to calculate the percentage is SUM(sum of all the amount up to 17 to 18 Waiting Times) / SUM(Sum of amount for the all the Waiting Times)

Can some one help me with the mdx to achive SUM(sum of all the amount up to 17 to 18 weeks) .

I tried the below. But the problem is , the numarator [Waiting Times].[Wait].&[17 To 18 Weeks] is just giving me the amount for that week. I need the sum of amount up to 17 to 18 [Waiting Times].

CREATE
MEMBER CurrentCube.[Measures].[Percent of Performance] AS
CASE
WHEN
IsEmpty([Measures].[Patients])
THEN NULL
ELSE
(
[Waiting Times].[Wait].&[17 To 18 Weeks]
,[Measures].[Patients]
)
/
(
[Waiting Times].[Wait].[All]
,[Measures].[Amount]
)

whytheq whytheq · Accepted Answer · 2015-10-15T11:12:49

If you want all members upto a certain member then you can use the range operator : with null i.e. null:[x]

So applying to your script:

CREATE 
  MEMBER CurrentCube.[Measures].[Percent of Performance] AS 
    IIF
    (
      IsEmpty([Measures].[Patients])
     ,NULL
     ,Divide
      (
        (
          NULL : [Waiting Times].[Wait].[17 To 18 Weeks]
         ,[Measures].[Patients]
        )
       ,(
          [Waiting Times].[Wait].[All]
         ,[Measures].[Amount]
        )
      )
    ) ;

If you try the following do you still get NULL, if so it means that your condition IsEmpty([Measures].[Patients]) is true?

CREATE 
  MEMBER CurrentCube.[Measures].[Percent of Performance] AS 
    IIF
    (
      IsEmpty([Measures].[Patients])
     ,NULL
     ,1
    ) ;

MDX percentage calculation

1 Answers