Use KPI in Table, Matrix and Card Visualisations in Power BI

Use KPI in Table, Matrix and Card Visualisations in Power BI

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”

Excel 2016 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 ServerPower Pivot Get External Data From
  • Enter server name and database name then click Next

Power Pivot Get Data From SQL Server

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

Power Pivot Get Data From SQL Server

  • After the data successfully imported click Close

Power Pivot Get Data From SQL Server

Continue reading “Use KPI in Table, Matrix and Card Visualisations in Power BI”

Webinar Materials: Power BI Under the Hood

Pass DW BI VC Power BI Under the Hood with Soheil BakhshiI’d like to thank you all for attending the webinar held on 30th September 2016. I talked about some amazing under cover aspects of Power BI Desktop model. In this session you learnt:

If you’ve missed the webinar you can watch it online here:

Download the Power Point presentation file here.:

Download (PPTX, 1.91MB)

Here is the PDF version of presentation:

Download (PDF, 534KB)

Data Classification in Power BI

Power BI Data ClassificationIn many corporations depending on the type of data is being used there could be different types of the sensitivities that should be applied to that data. Data Classification fulfills in Power BI Service this matter very easily. In today’s post you’ll learn how to setup Data Classification in Power BI Service.

First of all I want to inform you that Data Classification is NOT a sort of security or privacy setting. It is only a TAG which is all about informing Power BI users across a corporation to take extra care when they want to share data with other people inside or outside of that corporation. For instance some data might be OK to be shared externally outside the company, but, the other data might not be shared with groups of people even within that corporation.

Depending on your corporation you might have different levels of sensitivity like

  • High Sensitive Data
  • Medium Sensitive Data
  • Low Sensitive Data

So depending on what level of sensitivity, for instance for High Sensitive Data, the Power BI users should be really careful of who they share Power BI Dashboards and data with. In Power BI Service we can easily setup data classification on our dashboards so anyone who is looking at that dashboard is able to understand how sensitive that dashboard is and who they can share it with.

Requirements

To be able to setup Data Classification in Power BI Service you have to:

In case that you want to add another admin user,and if you already integrated your on-premises Active Directory with Azure Active Directory (AD) then you can either grant necessary admin rights to that user from your Azure portal in Azure AD or directly from Office 365 Admin Centre.

The user needs to be an Office 365 “Global Administrator” to be able to setup data classification in Power BI Service. A global administrator will have access to “Admin Portal” panel within Power BI Service which includes data classification and many more other important settings.

Make a User Global Administrator in Office 365

After you signed into your Power BI Service account,

  • Click “Admin” tile from the app launcher

Office 365 Admin Centre

Continue reading “Data Classification in Power BI”