Category Archives: Excel

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

Analyse Power BI Data in Excel

A while ago I wrote a blog post about Power BI Publisher for Excel. Today I want to explain some new features added to the publisher. In this post you learn how to analyse Power BI data in Excel. Using the new Power BI Publisher for Excel, not only can we pin an Excel range or chart to a Power BI dashboard directly from Excel, but also we are now able to easily connect to a Power BI service, select any group workspaces and analyse a desired report or dataset.

Requirements

  • Desktop versions of Microsoft Excel 2007 and later
  • Download and install Power BI Publisher for Excel
  • Power BI Publisher for Excel add-in will be enabled by default after you install it, however, if you don’t see the “Power BI” tab in the ribbon in Excel you can enable it from File –> Options –> Add-ins –> COM Add-ins –> tick Microsoft Publisher for Excel.

Enable Power BI Publisher for Excel

Connect to and Analyse Power BI Data in Excel

Analyse Power BI Service Reports or Datasets in Excel (From Power BI Service)

Previously we could analyse Power BI data in Excel directly from Power BI service by:

  • Log in to Power BI Service
  • Clicking ellipsis button of a desired dataset and clicking “Analyse in Excel”

Analyse Power BI Data in Excel from Power BI Service

  • Clicking ellipsis button of a desired report and clicking “Analyse in Excel”

Analyse Power BI Reports in Excel from Power BI Service

  • Doing either way, it downloads an “odc” file that could be opened in Excel.

Analyse Power BI Data in Excel from Power BI Service Enable Data Connection

  • Now you can analyse the data in Excel using pivot tables and pivot charts.

Analyse Power BI Data in Excel

Continue reading Analyse Power BI Data in Excel

How to Overcome Map Related Issues in Power BI, Power View and Power Map

One of the most powerful features in Power BI and Excel is supporting geospatial visualisations. In Excel we can use Map visualisation in Power View, or use Power Map directly. In Power BI, as you know, there are two built-in visualisations supporting geographic coordinate data, Map and Filled map. They work beautifully if you have enough data supported by Bing Maps. But, there are some issues with Map visualisations in both Power BI and Excel. In this post I address some of the issues I faced myself and I’ll provide the solutions for the issues. As “Filled Map” and “Map” visualisations in Power BI are very similar my focus in this post would be on “Map” visualisation. My intention is not explaining Power View and Power Map that much so my focus in this article would be on Power BI more than the other two.

Requirements

To experiment everything I explain in this post you need to have:

  • The new SQL Server sample, WideWorldImportersDW (WWI). You can download it here
  • The latest version on Power BI Desktop (current version is 2.35.4399.381 64-bit (May 2016))
  • Excel 2016 or Excel 2013

If you use Excel 2016, then you need to turn on Power View on.

Check this out if you want to learn more about BI features in Excel 2016.

Get Data in Power BI

  • Open Power BI Desktop
  • Get Data from SQL Server Database
  • Select Fact.Sales and Dimension.City then load data

Power BI Desktop

Map Issues In Power BI

Wrong Cities in Power BI

  • Expand the “Dimension City” table
  • Select “City” column then change its Data Category to City (Data Category is on “Modeling” tab from the ribbon)

Power BI Desktop Data Category

  • Put a Map visual into the page
  • Put “City” on Location
  • Put “Total Excluding Tax” on Size

Power BI Desktop Map

As you see sales distributed across different countries, but, this is not quiet right.

  • Put a slicer on the page then put “Country” on the slicer
  • Click “United States” to filter the Map

Power BI Desktop Slicer

Oops! This is not quiet right. What happened is that Bing Map Engine gets confused with the city names so that it shows a city with the same name outside of the US, just like New Plymouth which a city in New Zealand, but, the New Plymouth we have in our data source is the New Plymouth from Idaho in the US.

Continue reading How to Overcome Map Related Issues in Power BI, Power View and Power Map