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.
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]