KPIs and Power BI; Visualization Aspect

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2016-04-12_23h56_44

In every reporting solution you might feel KPI as a requirement. KPI or Key Performance Indicator is a measure for business to understand how they are acting in specific area that is very import for their business. Power BI supports built-in visualization for KPI, and there are also some good custom visuals that can be used for this purpose. In this post you will learn how to create KPI visualizations with Power BI.

What is KPI?

Key Performance Indicator is a measure for business to understand how they perform in specific area that is important and many times critical for their business. For example they would like to see how the current year to date sales is going against what they estimated? Is it higher or lower? What is the trend? Is it going upward or downward? There are some visualization options to show KPIs, and from them the most commons are; Gauges, Charts with arrows that shows upward or downward, and color code of red, amber, or green (visualizing the status).

Fortunately Power BI has a built-in KPI visualization (released 29th of Feb 2016) which can be used for this purpose. There are also some great custom visualizations that I found really useful and like to share them with you here.

2016-04-12_23h56_59

KPI Elements

KPIs measuring something and show trend and status. So they have components to work with as below:

  • Value; The main measure which we want to evaluate
  • Target; What we want to compre the Value with
  • Trend; How Value perfoms in a time period, is it going upward, downward…?
  • Status; What is the current status of Value against Target?

There might be other components as well, but let’s keep it simple. and work on these elements. In below example you’ll see how we apply attributes and configurations for each visualization element to visualize KPI there.

Prerequisite

For running examples of this post you need to install AdventureWorksDW like other examples of Power BI online book.

Built-in KPI

As I mentioned earlier KPI recently added to Power BI and you can find it in built-in visualization list in visualization pane.

2016-04-12_21h53_33

Using this item is easy as it only has three data fields to configure:

  • Indicator: This would be your KPI Value, or the measure to evaluate.
  • Trend Axis; dimension attribute to calculate trend on it. for example Months of year to check the trend over a period of year.
  • Target Goals; Your target value. something that KPI value will be compared with.

I’ve only used FactInternetSales and DimDate from AdventureWorksDW in this demo. For simplicity I got the target value as static value. I’ve created a measure in FactInternetSales called Target, and assigned the static value 1,000,000$.

2016-04-12_22h07_12

As I said this is just for simplicity. Your target value in real world scenarios might come from a forecast table, or a budget table or something like that.

In the report area, create a slicer on Calendar Year. and then drag and drop a KPI element into the report. set Indicator as SalesAmount, Target goals to be the Target measure, and Trend Axis to be EnglishMonthName (Before doing that you have to Sort EnglishMonthName column by MonthNumberOfYear in the Data Tab). Then this is what you get:

2016-04-12_22h14_30

As you see the KPI now shows me the value or indicator (which is Sales Amount) in the middle with large font ($1.73M), and the Goal or target under that with smaller font ($1M), and the percentage of the value compared with goal besides it (+73.18%).  You also see this colored green which means status is good or in the other words value is higher than expected. in the back ground you also see an area chart of value over period of months (Green area chart shows value over months). You can change the calendar year in slicer and see how the KPI change.
Now lets see another KPI and how to change Status on that. For the other KPI I want to see TotalProductCost as Indicator, and everything else exactly the same. For Costs usually the lower is better which is different from previous KPI. In the first KPI the higher value means the better result, the higher sales is desired. But for Costs I expect decrease. So I can simply change the Status in the Formatting area of KPI to have the direction as Low is good. and here would be the result;

2016-04-12_22h22_23

Now you see that despite the fact that TotalProductCost is greater than the goal still it shows it as red, because low is good, and high is bad. The default behavior for Status is that High is good.

Bullet Chart

Bullet chart is a custom visualization published by Microsoft. You need to download it from Visuals Gallery and then import it into Power BI. This chart has many fields to configure, you can configure them all or part of them. Required fields are Value, Target Value. You need to also set Minimum and Maximum to appropriate values. There are also areas with red, amber, and green colors that can be assigned with Needs Improvement, Satisfactory, Good, and Very Good fields. For this chart I’ve used a sample table with single line as below:

2016-04-12_22h39_07

And I configured Bullet chart with fields from this table combined with FactInternetSales, and DimDate as below;

2016-04-12_23h35_33

As you can see in the above chart which is for calendar year 2007; that for some months like December the sales amount is in good range (from $1M to $2M). for some months it is in amber range like August and June (between $500K and $1M), and for some it is below $500K and are in red range such as January.

As you see in above configuration I haven’t used Satisfactory and Good range. Have a play with this chart and configure it to see what you get out of it.

Dial Gauge

Another custom visualization which gives us the gauge visualization is DialGauge. This visual has been published by CloudFonts Technologies LLP. like the normal gauge for KPI this component gives you flexibility to define ranges for green, amber, and red. The Pointer Value is the value we want to evaluate. You can also set a percentage for this gauge. I’ve used this gauge with the sample table I’ve mentioned earlier in this post. and SalesAmount as the pointer value. For this chart I’ve used another slicer for the month in addition to the slicer for the year.

2016-04-12_23h45_41

As you see the pointer value stays in green area because sales amount of October 2007 is $1.08M which is between $1M and $2M. You can check what you see if you change the month to August (expect pointer to fall into amber area).

Linear Gauge

LinearGauge is the custom visual published by MAQ Software.  You can set minimum and maximum values, target value, and two trend values. As an example in below chart I’ve used SalesAmount as the value, and other information from the sample table above.

2016-04-12_23h51_38

As you see the target value showed with a vertical line in $1M point. the orange filled section shows the value which is sales amount $1.08M for month October 2007. Percentage against target is 108.04% which is +8.04%. Trend values shows that sales amount is going upwards compared to each trend value.

Summary

In this post you’ve seen some visualization items that are useful to show KPIs. There will be definitely more charts and custom visuals in upcoming months. However this post gave you a better feeling about how to use them, or what items to look for and configure when you deal with KPIs. If you are interested to learn more about Power BI, please continue reading Power BI online book.

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

3 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">