One of the coolest features in Power Pivot is the ability to define KPIs based on calculated measures. You can create KPIs in SSAS Tabular as well. Unfortunately, this feature is missing from Power BI. In this post I show you a very simple way to import KPIs and use them in Table, Matrix, Multi-row card and Card visualisations in Power BI.

I use the word “IMPORT” as this feature is NOT available in Power BI Desktop yet so we CANNOT create KPIs directly in Power BI Desktop, but, there is work around for it that I explain it in this post.

Requirements

• Latest version of Power BI Desktop
• Microsoft Excel (2007 or later)
• Power Pivot add-on if using Excel 2007 to 2013 (Power Pivot is already available in Excel 2016)
• Power Query add-on if you need to transform your data (Power Query is available only in Excel 2010 Professional Plus and Excel 2013. It’s added to Excel 2016 as a built-in feature. Check this out to find out more about BI features in Excel 2016.). In this post I’m not loading data using Power Query, so you can ignore Power Query if you want to follow this article to make your first sample KPI work.

How It Works

The work around is really easy. You only need to

• open Excel
• load data into Power Pivot model from your source
• create desired calculated measures in Power Pivot
• create desired KPIs on top of your calculated measure(s)
• save the Model (Excel file)
• import the Model to Power BI Desktop

Let’s go through the whole process step-by-step to see how it works on real world.

Note: I use Excel 2016 and Adventure Works DW SQL Server sample database. If you’re using prior versions of Excel, you have to download and install Power Pivot for Excel. All steps below are pretty much the same.

• Open Excel 2016
• From Data tab click “Manage Data Model”

Note: In case you’re using prior versions of Excel you need to click “Manage” from Power Pivot tab. All other steps would be the same.

• Get external data from SQL Server
• Enter server name and database name then click Next

• Select “FactResellerSales”, “DimProduct”, “DimProductCategory”  and “DimProductSubCategory” then click Finish

• After the data successfully imported click Close

• Create some simple calculated measures in FactResellerSales like below:

Total Product Costs:=SUM([TotalProductCost])

Reseller Sales:=SUM([SalesAmount])

Sales vs Product Costs:=sum([TotalProductCost])/sum([SalesAmount])

• Change the formatting of the first two measures to currency ($) and select percentage (%) for the third one

• Create KPIs on top of “Sales vs Product Costs” by right clicking on the measure then clicking “Create KPI”

• Click “Absolute value” then define status threshold as below

Note: The above KPI shows our sales status vs. product costs. If product cost is 65% or less than sales amount then the status is green which means everything is under control. If product cost is between 65% and 80% of sales amount then the sales status needs some attention and the status shows yellow. If product cost is more than 80% of sales amount then the sales status is not good and it shows red.

• After creating the KPI, a KPI icon adds to the “Sales vs Product Costs” calculated measure

• Save and close the Excel file

So far we created some measures and an  KPI in Power Pivot. Now it is time switch to Power BI and import the Power Pivot model.

• Open Power BI Desktop
• Select “Excel workbook Contents” from “Import” menu and import the Excel file you saved earlier

• Click Start

• After you successfully imported the data model from Excel click Close

• In Power BI Desktop put a Matrix visual on the page
• Expand “DimProductCategory” and click “EnglishProductCategoryName”
• Expand “DimProductSubCategory” and click “EnglishProductSubCategoryName”
• Expand “FactResellerSales” then click “Reseller Sales” and “Total Product Costs” measures
• You’ll also see a “Sales vs Product Costs” KPI
• Expand the KPI and click “Status”

As I mentioned before you can use KPIs in Matrix, Table, Card and Multi-card visualisations.

If you publish the model to Power BI service the reports shows the KPIs.

I hope Power BI development team add this feature to Power BI soon. Until then you can use the above workaround to show KPIs in Table, Matrix, Multi-card and Card visualisations.