[ad_1]
One of many coolest options in Energy Pivot is the flexibility to outline KPIs primarily based on calculated measures. You’ll be able to create KPIs in SSAS Tabular as nicely. Sadly, this function is lacking from Energy BI. On this publish I present you a quite simple technique to import KPIs and use them in Desk, Matrix, Multi-row card and Card visualisations in Energy BI.
I take advantage of the phrase “IMPORT” as this function is NOT obtainable in Energy BI Desktop but so we CANNOT create KPIs instantly in Energy BI Desktop, however, there’s 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 obtainable in Excel 2016)
- Energy Question add-on if you should rework your information (Energy Question is obtainable solely in Excel 2010 Skilled Plus and Excel 2013. It’s added to Excel 2016 as a built-in function. Examine this out to search out out extra about BI options in Excel 2016.). On this publish I’m not loading information utilizing Energy Question, so you possibly can ignore Energy Question if you wish to observe this text to make your first pattern KPI work.
The work round is very easy. You solely must
- 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.
Be aware: I take advantage of Excel 2016 and Journey Works DW SQL Server pattern database. When you’re utilizing prior variations of Excel, it’s important 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”
Be aware: In case you’re utilizing prior variations of Excel you should click on “Handle” from Energy Pivot tab. All different steps can be the identical.
- Get exterior information 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 information 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
Be aware: 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 every thing is below 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 just isn’t good and it reveals 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 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 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 “Complete 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 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 crew 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
[ad_2]