Use KPI in Desk, Matrix and Card Visualisations in Energy BI

One of many coolest options in Energy Pivot is the flexibility to outline KPIs primarily based on calculated measures. You may create KPIs in SSAS Tabular as properly. Sadly, this function is lacking from Energy BI. On this publish I present you a quite simple strategy to import KPIs and use them in Desk, Matrix, Multi-row card and Card visualisations in Energy BI.

I exploit the phrase “IMPORT” as this function is NOT accessible in Energy BI Desktop but so we CANNOT create KPIs immediately in Energy BI Desktop, however, there may be work round for it that I clarify it on this publish.

• Newest model of Energy BI Desktop
• Microsoft Excel (2007 or later)
• Energy Pivot add-on if utilizing Excel 2007 to 2013 (Energy Pivot is already accessible in Excel 2016)
• Energy Question add-on if it’s essential to rework your knowledge (Energy Question is offered solely in Excel 2010 Skilled Plus and Excel 2013. It’s added to Excel 2016 as a built-in function. Verify this out to search out out extra about BI options in Excel 2016.). On this publish I’m not loading knowledge utilizing Energy Question, so you may ignore Energy Question if you wish to comply with this text to make your first pattern KPI work.

The work round is very easy. You solely have to

• open Excel
• load knowledge into Energy Pivot mannequin out of your supply
• create desired calculated measures in Energy Pivot
• create desired KPIs on prime of your calculated measure(s)
• save the Mannequin (Excel file)
• import the Mannequin to Energy BI Desktop

Let’s undergo the entire course of step-by-step to see the way it works on actual world.

Notice: I exploit Excel 2016 and Journey Works DW SQL Server pattern database. When you’re utilizing prior variations of Excel, you need to obtain and set up Energy Pivot for Excel. All steps under are just about the identical.

• Open Excel 2016
• From Information tab click on “Handle Information Mannequin”

Notice: In case you’re utilizing prior variations of Excel it’s essential to click on “Handle” from Energy Pivot tab. All different steps could be the identical.

• Get exterior knowledge from SQL Server
• Enter server title and database title then click on Subsequent

• Choose “FactResellerSales”, “DimProduct”, “DimProductCategory”  and “DimProductSubCategory” then click on End

• After the info efficiently imported click on Shut

• Create some easy calculated measures in FactResellerSales like under:

Complete Product Prices:=SUM([TotalProductCost])

Reseller Gross sales:=SUM([SalesAmount])

Gross sales vs Product Prices:=sum([TotalProductCost])/sum([SalesAmount])

• Change the formatting of the primary two measures to forex ($) and choose share (%) for the third one

• Create KPIs on prime of “Gross sales vs Product Prices” by proper clicking on the measure then clicking “Create KPI”

• Click on “Absolute worth” then outline standing threshold as under

Notice: The above KPI reveals our gross sales standing vs. product prices. If product price is 65% or lower than gross sales quantity then the standing is inexperienced which implies all the things is underneath management. If product price is between 65% and 80% of gross sales quantity then the gross sales standing wants some consideration and the standing reveals yellow. If product price is greater than 80% of gross sales quantity then the gross sales standing is just not good and it reveals crimson.

• After creating the KPI, a KPI icon provides to the “Gross sales vs Product Prices” calculated measure

• Save and shut the Excel file

Thus far we created some measures and an  KPI in Energy Pivot. Now it’s time swap to Energy BI and import the Energy Pivot mannequin.

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

• After you efficiently imported the info mannequin from Excel click on Shut

• In Energy BI Desktop put a Matrix visible on the web page
• Develop “DimProductCategory” and click on “EnglishProductCategoryName”
• Develop “DimProductSubCategory” and click on “EnglishProductSubCategoryName”
• Develop “FactResellerSales” then click on “Reseller Gross sales” and “Complete Product Prices” measures
• You’ll additionally see a “Gross sales vs Product Prices” KPI
• Develop the KPI and click on “Standing”

As I discussed earlier than you should use KPIs in Matrix, Desk, Card and Multi-card visualisations.

When you publish the mannequin to Energy BI service the stories reveals the KPIs.

I hope Energy BI growth staff add this function to Energy BI quickly. Till then you should use the above workaround to indicate KPIs in Desk, Matrix, Multi-card and Card visualisations.

Associated