KPIs in Power BI semantic models

DataZoe
5 days ago
3 min read

Key performance indicators (KPIs) are a way to set a target, as well as logic and graphics to show for status and the trend for any measure in a Power BI semantic model. With the KPI added to the measure, the semantic model users can understand the context of if this measure, that is, if the value shown is considered a good value or bad value.

These measure KPIs work in Power BI reports. Unfortunately, there is no button to create these in Power BI Desktop or web modeling. But now it's possible with the addition of TMDL view!

This is similar to perspectives, which I have a blog post about at Perspectives in Power BI semantic models.

Let's get started with an example!

Here I have a measure [Total sales], simply summing the sales values. These sales values are for each country and segment and month over 2013 and 2014. I added a target of $150,000 per row. And added the KPI properties to [Total sales] to compare how we did towards this target and how we are doing year over year.

Goal or target: I simply referenced the [Sales target] measure I had created.

Status: I calculated the % attainment to target and assigned -1 to less than 95%, 0 to 95% to 100%, and 1 to greater than 100% attainment.

Status graphic: Shapes

Trend: I calculated if this number is increasing (1), staying the same (0), or decreasing (-1) since prior year.

Trend graphic: Standard arrow

Now I can see November for Canada not only missed it target for that month and is also decreasing year over year for that month. Every other month, quarter, and overall is meeting the target. Now I have the full context of what this measure is supposed to show me.

How did I do this?

Open Power BI Desktop and load the sample file, the financials table.
I added a Date table and created a relationship between the Date fields. I used this TMDL script: DAX Date table in TMDL
I added target measure and measure with KPIs using this TMDL script.

createOrReplace

	ref table financials

		measure 'Sales target' = ```
				
						SUMX(
							financials,
							150000
						)
				
				
				```
			formatString: \$#,0;(\$#,0);\$#,0
			lineageTag: b2e9a6c1-7027-4e48-9a74-067d7cf4e9a3

		/// Calculates the sales, with discount applied
		measure 'Total sales' = SUM(financials[Sales])
			formatString: \$#,0;(\$#,0);\$#,0
			lineageTag: 7bcfc48f-9dc3-4a0c-9b74-b6b102c91d68

			kpi
				targetExpression = [Sales target]
				targetFormatString: $#,##0
				statusGraphic: Shapes
				statusDescription: Above 100% achieves target, warning at 95%, and less than 95% is a miss.
				statusExpression =
					VAR x = DIVIDE(
						[Total sales],
						[_Total sales Goal]
					)
					RETURN
						IF(
							ISBLANK(x),
							BLANK(),
							IF(
								x < 0.95,
								-1,
								IF(
									x < 1,
									0,
									1
								)
							)
						)
				trendGraphic: Standard Arrow
				trendDescription: The year over year trend.
				trendExpression = ```
					VAR x = DIVIDE(
						[Total sales],
						CALCULATE(
							[Total sales],
							'Date'[Year] = MAX('Date'[Year]) - 1
						)
					)
					RETURN
						IF(
							ISBLANK(x),
							BLANK(),
							IF(
								x < 1,
								-1,
								IF(
									x = 0,
									0,
									1
								)
							)
						)
					
					```

			annotation PBI_FormatHint = {"currencyCulture":"en-US"}

Then I created the report by simply using the fields from the Data pane.

I did use DAX query view to help me create the expressions for Status and Trend, and there are more graphics to choose from. Some have 3 levels (-1, 0, 1) and others have 5 (-2,-1,0,1,2). You can set the graphic to None if you want to set it in the visual itself too. There are other options such as these.

None
Shapes
Road Signs
Five Boxes Colored
Five Bars Colored
Guage - Ascending
Guage - Descending
Three Flags Colored
Three Stars Colored
Three Circles Colored
Three Symbols Uncircled Colored