This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. 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.

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.

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

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.

Parts of this blog
Key Performance Indicators (KPIs) Creating relative KPIS in SSAS Tabular (this blog) Absolute KPIs in SSAS Tabular

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC