| Simple Gross Profit Calculation |
[Measures].[Sales Amount] - [Measures].[Total Product Cost]
|
| Sales in the USA |
([Measures].[Sales Amount], [Customer].[Country].&[United States])
|
| Year to Date Sales (Works for any level of Date Hiearchy) |
Aggregate(
PeriodsToDate( [Date].[Calendar Hierarchy].[Year],
[Date].[Calendar Hierarchy].CurrentMember ),
([Measures].[Sales]))
|
| Alternate Year To Date Expression (YTD, QTD, MTD) |
AGGREGATE (
YTD ([Date Order].[Calendar].CurrentMember),
Measures.[Sales Amount])
|
| Product Ranking |
IIF (Product.Product.CurrentMember IS Product.Product.[All],NULL,
IIF (Measures.[Sales Amount] = 0, NULL,
RANK(Product.Product.CurrentMember, ORDER (Product.Product.Members, Measures.[Sales Amount]))))
|
| Sales from 365 Days Ago |
(ParallelPeriod([Invoice Date].[Date Hierarchy].[Date], 365, [Invoice Date].[Date
Hierarchy].CurrentMember), [Measures].[Sales Amount])
|
| Sales in the Previous Period |
(Measures.[Sales Amount], [Date Order].[Calendar].CurrentMember.PrevMember)
|
| Top 10 Selling Products (Named Set) |
TopCount (Product.Product.Product.Members,10,Measures.[Sales Amount])
|
| Three Years Average Sales From NOW() |
Avg(
{ParallelPeriod( [Date].[Date].[Year], 3,
StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")) :
StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")} , [Measures].[Sales Count])
|
| Drill-through Action Caption |
'Get Sales Details for'
+[Product].[Product].CurrentMember.Member_Caption
|
| Change SSAS Calculation Text color |
IIF([Measures].[Profit Percentage] < .40, 255 , 0)
|
| Changing a Calculation with a SCOPE statement |
SCOPE ([Measures].[Scope Profit]);
THIS = ([Measures].[Sales Amount] - [Measures].[Standard Product Cost]);
END SCOPE;
|
| Clear Ration Value when at all level |
SCOPE ([Customer].[Customer Geography].[All], Measures.RatioOverParent);
THIS = NULL
|
| SSAS KPI Value Expression |
[Measures].[Sales Amount] * 1.2
|
| SSAS KPI Goal Expression |
Case
When IsEmpty
(ParallelPeriod
([Date Order].[Fiscal].[Fiscal Year],
1,[Date Order].[Fiscal].CurrentMember))
Then [Measures].[Sales Amount]
Else 1.10 *
([Measures].[Sales Amount],
ParallelPeriod([Date Order].[Fiscal].[Fiscal Year],
1,[Date Order].[Fiscal].CurrentMember))
End
|
| SSAS KPI Status Expression |
Case
When KpiValue( "Sales Revenue YTD" ) / KpiGoal( "Sales RevenueYTD" ) > 1
Then 1
When KpiValue( "Sales Revenue YTD" ) / KpiGoal( "Sales Revenue YTD" ) <= 1 And KpiValue( "Sales Revenue YTD" ) / KpiGoal( "Sales Revenue YTD" ) >= .85
Then 0
Else -1
End
|
| SSAS KPI Trend Expression |
Case
When IsEmpty
(ParallelPeriod
([Date Order].[Fiscal].[Fiscal Year],
1,[Date Order].[Fiscal].CurrentMember))
Then 0
When VBA!Abs
((KpiValue( "Sales Revenue YTD" )- (KpiValue( "Sales Revenue YTD" ),
ParallelPeriod( [Date Order].[Fiscal].[Fiscal Year],
1, [Date Order].[Fiscal].CurrentMember)))
/(KpiValue( "Sales Revenue YTD" ),
ParallelPeriod
( [Date Order].[Fiscal].[Fiscal Year],
1,[Date Order].[Fiscal].CurrentMember))) <=.02 Then 0 When (KpiValue( "Sales Revenue YTD" )- (KpiValue( "Sales Revenue YTD" ), ParallelPeriod ( [Date Order].[Fiscal].[Fiscal Year], 1, [Date Order].[Fiscal].CurrentMember))) /(KpiValue( "Sales Revenue YTD" ), ParallelPeriod( [Date Order].[Fiscal].[Fiscal Year],1,[Date Order].[Fiscal].CurrentMember)) >.02
Then 1
Else -1
End
|
