MDX- CALCULATION BY ITEM()

最新推荐文章于 2022-06-09 10:00:07 发布

iteye_6233

最新推荐文章于 2022-06-09 10:00:07 发布

阅读量260

点赞数

WITH MEMBER [measures].[First Year Internet Sales Amount] 
AS    
     {{[Date].[Calendar Year].&[2006],[Date].[Calendar Year].&[2007],[Date].[Calendar Year].&[2008]}     
       *{ [Measures].[Internet Sales Amount]}
      }.Item (0) // Item(0) is used to get the first year measure
   , FORMAT_STRING = "Currency"
   , SOLVE_ORDER = 1
MEMBER [measures].[Variance]  AS
(
[Measures].[Internet Sales Amount] - [measures].[First Year Internet Sales Amount]
)
, SOLVE_ORDER = 2
MEMBER [Measures].[PercentageVariance] AS
(
CASE WHEN [measures].[First Year Internet Sales Amount] IS NULL 
OR ISEMPTY([measures].[First Year Internet Sales Amount]) THEN 1
     WHEN [measures].[Internet Sales Amount] IS NULL THEN -1
ELSE [measures].[Variance] 
 /
[measures].[First Year Internet Sales Amount] 
END )
,FORMAT_STRING = "Percent"
, SOLVE_ORDER = 3  
SELECT 
   {  [measures].[First Year Internet Sales Amount],[Measures].[Internet Sales Amount] 
     , [measures].[Variance],[Measures].[PercentageVariance]
    }
  ON 0
, [Date].[Calendar Year].children ON ROWS
FROM 
(select 
{[Date].[Calendar Year].&[2006],[Date].[Calendar Year].&[2007],[Date].[Calendar Year].&[2008]} on 0 
from 
[Adventure Works]
)

First Year Internet Sales AmountInternet Sales AmountVariancePercentageVariance
CY 2006$6,530,343.53$6,530,343.53$0.000.00%
CY 2007$6,530,343.53$9,791,060.30$3,260,716.7749.93%
CY 2008$6,530,343.53$9,770,899.74$3,240,556.2149.62%