BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Key Performance Indicators (KPIs)
- Creating relative KPIS in SSAS Tabular (this blog)
- 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.
- Key Performance Indicators (KPIs)
- Creating relative KPIS in SSAS Tabular (this blog)
- Absolute KPIs in SSAS Tabular