Combining two calculated measures and using the combination result in MDX

Question

I am creating two different calculated measure and I want to use the result of both calculated measure in one query from a same cube.

One of the calculated measure created is -

With Member [Measures].[VenueSalesCost]
 as
 (
 [Measures].[Amount - Reporting Currency]
)
Select [Measures].[VenueSalesCost]
 on columns
 from  [Project accounting cube]
where 
[Chart of accounts].[Main account name].&[Venue Hire Costs]

and the second is --

 With Member [Measures].[VenueSalesAmount]
as (
[Measures].[Amount - Reporting Currency]
)
 Select [Measures].[VenueSalesAmount]
 on columns
 from  [Project accounting cube]
where 
[Chart of accounts].[Main account name].&[Rental of Venue]

Now i want to use both the measures in my query.please let me know how to combine the two measure.

Your measures are exactly the same. What differs are the WHERE conditions of the queries. What do you want to achieve? One query with the results of both queries in two columns? — FrankPl, Jan 23 '14 at 08:06
I want to use the above two measure and make a third measure and the logic is [Measures].[VenueSalesCost]-[Measures].[VenueSalesAmount] — Ayush Khanduri, Jan 23 '14 at 09:22

score 0 · Accepted Answer · answered Jan 23 '14 at 10:02

Just move the WHERE condition of the different queries into the defining tuple of the calculated measures:

With Member [Measures].[VenueSalesCost]
     as
     (
     [Chart of accounts].[Main account name].&[Venue Hire Costs],
     [Measures].[Amount - Reporting Currency]
     )
     Member [Measures].[VenueSalesAmount]
     as 
     (
     [Chart of accounts].[Main account name].&[Rental of Venue],
     [Measures].[Amount - Reporting Currency]
     )
     Member [Measures].[new Measure]
     as 
     [Measures].[VenueSalesCost] - [Measures].[VenueSalesAmount]
Select {
       [Measures].[VenueSalesCost],
       [Measures].[VenueSalesAmount],
       [Measures].[new Measure]
       }
       on columns
 from  [Project accounting cube]

@AyushKhanduri In that case, you should mark the answer as the correct one (click on the check mark, which turns it green). — FrankPl, Jan 23 '14 at 10:21

Combining two calculated measures and using the combination result in MDX

1 Answers1