564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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 ...
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.
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
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 2023. All Rights Reserved.