WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 519 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

How to create absolute and relative KPIs in SSAS Tabular
Part two of a three-part series of blogs

You can create key performance indicators (KPIs) within Analysis Services Tabular, but there a couple of foibles that you need to be aware of, as shown by this blog.

1. Key Performance Indicators (KPIs)
2. Creating relative KPIS in SSAS Tabular (this blog)
3. Absolute KPIs in SSAS Tabular

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 28 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Creating relative KPIS in SSAS Tabular

For the example on this page, I'll show how to set a KPI which displays the following statuses:

Threshold Status
Under 95% of sales for the same period last year Red circle
95-100% of sales for the same period last year Amber circle
More than 100% of sales for the same period last year Green circle

Here's a reminder of what I expect to see:

This is what I'm aiming to get at the end of this page.

## Step 1 - creating the necessary measures

For this sort of KPI (the most common one), you first need to create two measures: one for the thing you're measuring, and one for the thing you're using as a target.  For this year's sales, we can just sum sales:

This year:=SUM([Quantity])

For sales for the same period last year, we could use this measure:

Previous year:=CALCULATE(

SUM('Transaction'[Quantity]),

SAMEPERIODLASTYEAR('Calendar'[Date])

)

That is, sum total quantity sold, using figures for the same period but in the previous year.

## Step 2 - creating your KPI

The next thing to do is to create your KPI.  To do this, right-click on the measure which you're trying to report on:

Right-click on the total sales measure, and choose to turn it into a KPI.

You can now complete the dialog box which appears, using the numbered steps below as a guide:

Follow the steps below to complete this dialog box.

The steps are as follows:

Step Notes
1 SSAS Tabular will show here the measure that you're trying to track.
2 Choose your target measure from the drop list.
3 Either type numbers into the tiny text boxes, or click and drag on the black handles below them to move them.  You can also change the type of target by clicking on the diagrams.  From top to bottom and left to right, these aim for (respectively) as high as possible, as low as possible, as near a value as possible or as far away from a value as possible.
4 Choose an icon style (but note that a bug means that the left-hand option will be used regardless of what you pick!).
5 Optionally, click to type in descriptions for each part of your KPI.  There is very little point in doing this.

SSAS Tabular will show a tiny KPI symbol next to your measure:

Yeah - you have a KPI!

## Using the KPI in a pivot table

When you create a pivot table based upon a model containing a KPI, you can select the various components of it:

The pivot table fields for the pivot table shown below.

This would produce this pivot table:

The initial pivot table has a few problems with it.

The first thing I would do is deselect the trend (it will always show a horizontal arrow, as you can't set a trend for a KPI in SSAS Tabular - so why can you choose it?):

There's no point displaying something which can't change.

The next thing I do is to centre the Status column:

Surely you'd always want this column to be centre justified?

What you can't do, it seems, is to get the icon set you chose - as I've mentioned several times already, you get the one shown above regardless of what you asked for.  If your icons appear as numbers, deselect and reselect them to get the pictures instead.

That's about all there is to say about relative KPIs (they show up nicely in SSRS reports based on tabular models, by the way).  For the final part of this blog I'll just show how to create absolute KPIs, which are similar.

1. Key Performance Indicators (KPIs)
2. Creating relative KPIS in SSAS Tabular (this blog)
3. Absolute KPIs in SSAS Tabular
This blog has 0 threads