SSAS Trending KPI

KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is trending in right direction based on previous levels. If you want to show a measure has increased or decreased since last month, this can be accomplished by using the MDX function PrevMember. You want the trend to show at the month level only in excel. This way the users are not confused by extra trend graphics. This example will be using the adventure works cube.

Create a KPI in the cube and select the measure you want to compare.

clip_image001

The Goal will be a case statement to determine if the user is looking at the month level. If the user is not at the month level the goal will be null. If the user is at the month level then the goal will be a tuple of the previous month.

Case When ([Due Date].[Date].CurrentMember.level is [Due Date].[Date].[Month Name]) then

([Due Date].[Date].prevmember,[Measures].[Sales Amount Internet])

Else Null

End

clip_image003

The trend will check for the goal value, if the goal is null then the trend will be null. This ensures the trend only shows on the month level. If the goal is not null then the tuple for the previous month is compared to the KPI Value. Based on this comparison you will return a 1,0, or -1.

Case

When Isempty(KPIGoal(“Monthly Internet Sales”)) Then Null

When ([Due Date].[Date].prevmember, [Measures].[Sales Amount Internet]) <

KPIValue(“Monthly Internet Sales”) Then 1

When ([Due Date].[Date].prevmember, [Measures].[Sales Amount Internet]) =

KPIValue(“Monthly Internet Sales”) Then 0

Else -1

End

clip_image005

This is what it looks like in excel.

clip_image006

Advertisements
This entry was posted in SSAS, Syndication and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s