/*-- Aggregate leaf data -----------------------------------------------*/
Calculate ;
/*-----------------------------------------------------------------------------
| Internet Sales Calculations |
-----------------------------------------------------------------------------*/
Create Member CurrentCube.[Measures].[Internet Gross Profit]
As [Measures].[Internet Sales Amount] -
[Measures].[Internet Total Product Cost],
Format_String = "Currency",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior =
{
[Internet Sales Amount],
[Internet Total Product Cost]
} ;
Create Member CurrentCube.[Measures].[Internet Gross Profit Margin]
As ( [Measures].[Internet Sales Amount] -
[Measures].[Internet Total Product Cost] )
/
[Measures].[Internet Sales Amount],
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior =
{
[Internet Sales Amount],
[Internet Total Product Cost]
} ;
Create Member CurrentCube.[Measures].[Internet Average Unit Price]
As [Measures].[Internet Unit Price] /
[Measures].[Internet Transaction Count],
Format_String = "Currency",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior = [Internet Unit Price] ;
Create Member CurrentCube.[Measures].[Internet Average Sales Amount]
As [Measures].[Internet Sales Amount] /
[Measures].[Internet Order Count],
Format_String = "Currency",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior = [Internet Sales Amount] ;
Create Member CurrentCube.[Measures].[Internet Ratio to All Products]
As [Measures].[Internet Sales Amount]
/
(
Root( [Product] ),
[Measures].[Internet Sales Amount]
),
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior = [Internet Sales Amount] ;
Create Member CurrentCube.[Measures].[Internet Ratio to Parent Product]
As Case
When [Product].[Product Categories].CurrentMember.Level.Ordinal
= 0
Then 1
Else [Measures].[Internet Sales Amount]
/
( [Product].[Product Categories].CurrentMember.Parent,
[Measures].[Internet Sales Amount] )
End,
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales' ;
Create Member CurrentCube.[Measures].[Growth in Customer Base]
As Case
When [Date].[Fiscal].CurrentMember.Level.Ordinal = 0
Then "NA"
When IsEmpty
(
(
[Date].[Fiscal].CurrentMember.PrevMember,
[Measures].[Customer Count]
)
)
Then Null
Else (
( [Date].[Fiscal].CurrentMember, [Measures].[Customer Count] )
-
( [Date].[Fiscal].PrevMember, [Measures].[Customer Count] )
)
/
( [Date].[Fiscal].PrevMember,[Measures].[Customer Count] )
End,
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales' ;
Create Set CurrentCube.[Long Lead Products]
As Exists
(
[Product].[Model Name].[Model Name].Members,
[Product].[Days to Manufacture].&[4]
)
,
Display_Folder = 'Sets' ;
Create Set CurrentCube.[Core Product Group]
As DrillDownMember
(
[Product].[Product Categories].[Category].Members,
[Product].[Product Categories].[Category].[Bikes]
)
,
Display_Folder = 'Sets' ;
Create Dynamic Set CurrentCube.[Top 25 Selling Products]
As TopCount
(
[Product].[Product].[Product].Members,
25,
[Measures].[Sales Amount]
)
,
Display_Folder = 'Sets' ;
Create Dynamic Set CurrentCube.[Top 50 Customers]
As TopCount
(
[Customer].[Customer].[Customer].Members,
50,
[Measures].[Internet Sales Amount]
)
,
Display_Folder = 'Sets' ;
Create Dynamic Set CurrentCube.[Negative Margin Products]
As Filter
(
[Product].[Subcategory].[Subcategory],
[Measures].[Gross Profit Margin] < 0
)
,
Display_Folder = 'Sets' ;
|