Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
KPIs are a good idea, with some serious implementation issues (at least in my experience). Here's what they look like:
Here our aim is that this period's sales should be at least as big as last year's. The red circles show that we're failing completely in April, July and December, while the yellow ones shows that we're failing a bit in October and November.
A KPI has 3 main components:
Component | Notes |
---|---|
Value | The thing we're measuring. |
Goal | What we would like it to equal. |
Status | How well we're doing against this target. |
Note that Excel pivot tables imply that there is a fourth component called Trend, but you can't set this in SSAS Tabular.
In theory you can choose from the following range of icons for your KPI display:
The palette of icons theoretically available.
In practice it seems that if you're using Excel 2013 at any rate, the only set you'll ever get is the one shown on the left.
Can this really be true? I've found a couple of others sites reporting the same problem. I've left a question on StackOverflow and no one has contradicted me. It really does appear that this is a bug!
OK - now I've got my gripes about KPIs in SSAS Tabular out of the way, let's look at how they work!
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.
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.
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!
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.
Instead of aiming to hit another measure, you can set a numerical target or an absolute KPI. This is most useful when you want a ratio to be as large (or small) as possible.
We'll solve the relative KPI problem shown in the previous part of this blog by targeting the percentage ratio between this period's sales and sales for the same period last year:
Percent of last year:=IFERROR(
sum([Quantity]) /
CALCULATE(
SUM('Transaction'[Quantity]),
SAMEPERIODLASTYEAR('Calendar'[Date])
),BLANK())
That is, divide this period's sales by the total quantity for the same period in the previous year (if this generates a divide by zero error because there is no previous year, show a blank instead).
Having created the measure above, you can then turn it into a KPI:
Right-click on the measure and choose to create a KPI.
Complete the dialog box which appears as below by following the numbered steps:
The numbered steps are explained in more detail below.
The steps to follow are:
Step | What to do |
---|---|
1 | Choose to target an absolute value of 100% (which you have to type in as 1). |
2 | Specify thresholds when the icon colours will change from red to amber to green, either by typing into the miniature text boxes or by clicking and dragging on the black handles. |
3 | Choose what sort of target you want (here we're going for the highest ratio we can). |
4 | Choose the icon set you'd like to see, while recognising that you'll get the one on the left whatever you choose! |
You can now create a pivot table based upon this model in the same way as for a relative KPI (see the previous part of this blog), with one exception:
There's no point (as here) choosing to show the Goal, because it will always be the same!
If you display the goal as above, this column will lack variety:
The goal is the same for every single row!
And with that you've come to the end of this blog on creating KPIs within SSAS Tabular.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.