| Statement |
Expression |
| To calculate Simple Gross Profit |
[Measures].[Sales Amount] – [Measures].[Total Product Cost] |
| To calculate sales in a particular country e.g. (Canada) |
([Measures].[Sales Amount], [Customer].[Country].&[Canada]) |
| To calculate year to date sales of any level of date hierarchy |
AGGREGATE(
PeriodsToDate( [Date].[Calendar Hierarchy].[Year],
[Date].[Calendar Hierarchy].CurrentMember ),
([Measures].[Sales])) |
| Alternate year to date expression |
AGGREGATE (
YTD ([Date Order].[Calendar].CurrentMember),
Measures.[Sales Amount]) |
| To check the 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 done in the previous period |
(Measures.[Sales Amount],
[Date Order].[Calendar].CurrentMember.PrevMember) |
| To view top 10 selling products |
TopCount
(Product.Product.Product.Members,10,Measures.[Sales Amount]) |
| To display the average sales from three years till date |
Avg(
{ParallelPeriod( [Date].[Date].[Year], 3,
StrToMember(“[Date].[Date].&[” + Format(now(), “yyyyMMdd”) + “]”)) :
StrToMember(“[Date].[Date].&[” + Format(now(), “yyyyMMdd”) + “]”)} , [Measures].[Sales Count]) |
| Drillthrough Action caption |
‘Get Sales Details for’
+[Product].[Product].CurrentMember.Member_Caption |
| To change SSAS Calculation text color |
IIF([Measures].[Profit Percentage] < .40, 255 , 0) |
| Changing a calculation using scope statement |
SCOPE ([Measures].[Scope Profit]);
THIS = ([Measures].[Sales Amount] – [Measures].[Standard Product Cost]);
END SCOPE; |
| Clear ration value when at all levels |
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)))<=.02
(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 |