One of many coolest options in Energy Pivot is the power to outline KPIs primarily based on calculated measures. You’ll be able to create KPIs in SSAS Tabular as nicely. Sadly, this characteristic is lacking from Energy BI. On this submit 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 characteristic is NOT accessible in Energy BI Desktop but so we CANNOT create KPIs straight in Energy BI Desktop, however, there’s work round for it that I clarify it on this submit.
- 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 good to remodel your information (Energy Question is accessible solely in Excel 2010 Skilled Plus and Excel 2013. It’s added to Excel 2016 as a built-in characteristic. Verify this out to seek out out extra about BI options in Excel 2016.). On this submit I’m not loading information utilizing Energy Question, so you possibly can 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 information 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.
Word: I exploit Excel 2016 and Journey Works DW SQL Server pattern database. For those who’re utilizing prior variations of Excel, you must 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”
Word: In case you’re utilizing prior variations of Excel it’s good to click on “Handle” from Energy Pivot tab. All different steps could be the identical.
- Get exterior information from SQL Server
- Enter server identify and database identify then click on Subsequent
- Choose “FactResellerSales”, “DimProduct”, “DimProductCategory” and “DimProductSubCategory” then click on End
- After the information efficiently imported click on Shut
- Create some easy calculated measures in FactResellerSales like under:
Whole 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 foreign money ($) and choose proportion (%) 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
Word: The above KPI exhibits our gross sales standing vs. product prices. If product value is 65% or lower than gross sales quantity then the standing is inexperienced which implies all the things is underneath management. If product value is between 65% and 80% of gross sales quantity then the gross sales standing wants some consideration and the standing exhibits yellow. If product value is greater than 80% of gross sales quantity then the gross sales standing will not be good and it exhibits purple.
- After creating the KPI, a KPI icon provides to the “Gross sales vs Product Prices” calculated measure
- Save and shut the Excel file
Up to now we created some measures and an KPI in Energy Pivot. Now it’s time change 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 information mannequin from Excel click on Shut
- In Energy BI Desktop put a Matrix visible on the web page
- Increase “DimProductCategory” and click on “EnglishProductCategoryName”
- Increase “DimProductSubCategory” and click on “EnglishProductSubCategoryName”
- Increase “FactResellerSales” then click on “Reseller Gross sales” and “Whole Product Prices” measures
- You’ll additionally see a “Gross sales vs Product Prices” KPI
- Increase the KPI and click on “Standing”
As I discussed earlier than you should utilize KPIs in Matrix, Desk, Card and Multi-card visualisations.
For those who publish the mannequin to Energy BI service the stories exhibits the KPIs.
I hope Energy BI improvement staff add this characteristic to Energy BI quickly. Till then you should utilize the above workaround to point out KPIs in Desk, Matrix, Multi-card and Card visualisations.
