Tabular databases give us the ability to create KPI’s for business metrics, which can be used to visually represent performance of a business metric of actual performance to target for quick display to a business unit or manager. If you’ve created KPI’s in Power Pivot, creating KPI’s in a tabular project is very similar. To create our KPI, we’re going to need at least 2 different pieces of information: what our actual performance is and what our target performance is. Most of the time target performance is defined by comparing this time period to last year (or the same time period within last year). This tells us if our business is doing better than it was a year ago, or worse. Let’s start with our Adventure Works tabular database by creating 2 measures: the sum of our internet sales, and the sum of out internet sales last year. On a side note, make sure your date dimension is marked as the date dimension, or your time intelligence function won’t work.
Our internet sales measure looks like:
Wow, that was easy. Next is our Sales for last year.
LastYearSales:=CALCULATE(SUM(‘Internet Sales'[SalesAmount]), DATEADD(‘Date'[FullDateAlternateKey], -1, YEAR))
Now we have the 2 measures that we need. Right click on the first measure we created, about sales, and select “Create KPI”
Once the screen opens, you’ll then get the KPI screen, which looks like:
First we want to make sure that the name of our measure is the value. This is the value at the very top of the screen. Under the “Define Target Value” section, we’re selecting what we’re measuring against. In this case, it’s the measure called “Last Year Sales.” Additionally, if we wanted to measure our performance against an absolute value “like “10 million dollars in sales” we would just select the absolute value radio button and hard code the value in the field.
Underneath is where we specify what percentage to the target value we want to measure to. Just drag the slider bar until it is at the desired percentage of target that you want to see. Select the icons you want to use, and then click ok. Tabular will process it for a second and then indicate on the screen next to the measure that there is a KPI now associated with it.
And that’s it. Super simple to set up, visually super cool.