Wednesday, 21 July 2021

The Microsoft Power BI KPI Visual in Practice

The KPI Visual is a summary that compares an actual value to a target value, together with the percentage variance. It displays in red when below target, and in green when above target. The background shows the trend of the actual value over time.


The KPI visual requires two values, a target and actual value, plus a time interval. These three values mean you have to be clear about how to:

  •          Express your goal as a single number.
  •          Measure on-going progress.
  •          Have a system to record accurate data.

Although simple, these values go to the heart of a goal, and how you intend to achieve it.

Many different numbers may contribute to achieving the goal, but you must choose the one which best represents the target. Jim Collins would argue that being able to simplify complexity into a single number is a leadership skill that unifies people, and guides decisions. In his book, Good to Great, he talks of choosing the right economic denominator to drive profitability. The concept is the same as distilling a destination, or goal, into a single metric that can be tracked. Collins is not the only fan of headline numbers, John Doerr in Measure what Matters makes the case for clear goals with clear metrics. Marissa Mayer famously said, “It’s not a key result unless it has a number.”  

That so many great minds have felt the need to say anything at all indicates that this is neither obvious nor easy.

Having decided on what you will measure, the question of how to measure arises. Again, not obvious nor without its pitfalls. Can it be measured automatically, using IOT perhaps from a production line or a smart phone? If not, do people have to input the data, in which case how accurate will it be, and how onerous on those that have to do this work? Unless the process is made super easy, estimating, sloppy categorising, and all sorts of other shortcuts get taken. Including the final shortcut – not recording the data.

All of these things happen, all too commonly simply because people are already busy with the job they were hired to do.

Antoine de Saint-Exupery said, apparently, that “A goal without a plan is just a wish.” Many others have pointed out that “the road to hell is paved with good intentions.” Both pointing out that just setting a goal is insufficient, regardless of the talent or motivation of those setting the goal.

Hitting difficult goals is more about planning, monitoring, and adjusting actions accordingly. In other words, behind every serious goal is a system for monitoring progress. Once you have that, you have a very real possibility of putting meaningful numbers into the KPI visual.

The good news is that you may well have several systems that already contain the information you need. Most companies have data in databases and spreadsheets, some of which can be used to develop and monitor your KPIs. Or you may need to adapt or add a new system.

Power BI has enormous flexibility in allowing you to use data from various sources and manipulate it into something you can use. And once you’ve done that, you have a meaningful KPI that can be communicated to stakeholders.

If you’ve looked at the KPI visual and decided it’s not for you for any reason, think again. It’s simplicity is its power, and the hard work required to get the data it needs will be repaid many times over.

If you’d like to find out more about using your existing data to report on KPIs or other reporting, get in touch.

Tuesday, 21 July 2020

Understanding KPIs in Microsoft Power BI

Amongst the many different visuals you can select in Power BI, there is a KPI visual.


Whilst it is certainly not the only visual available for measuring important indicators, you should consider it when reporting on KPIs. 

So what is a KPI? A Key Performance Indicator, or KPI, is an important indicator, or value, that tells you whether or not you are on target to achieve something. Microsoft says that the KPI visual is designed to answer the question “Am I ahead or behind my target?” It is not a complex idea, although implementing KPIs so they are really effective takes some skill.

The name says it all:

Key = important.
Performance = the thing that needs to be happen.
Indicator = the numeric value to measure and compare.

The numeric value can be anything that you can measure, such as:

  • Money – sales, profit, etc.
  • Production output – number of widgets produced, number of articles published, etc.
  • Accidents – ideally none.
  • Patients – admitted, readmitted, recovered, died.
  • Training sessions attended.

KPIs are often lead indicators. That is to say, they are not lag indicators.

Lag indicators are the result of something happening, whereas lead indicators are the actions that lead to the result and the lag indicator. That’s complicated to explain, but easy with examples.

For instance, better trained people are likely to sell more, so if you want to increase sales, measure the number of training courses attended by your salesforce. If you want to lose weight, the amount you weigh is the lag indicator, but calories consumed or time spent exercising are lead indicators.

Lead indicators represent the things we can actually influence, which is why they make good KPIs.

Ideally, it must be something that you can measure at internals and compare against a target value.

Now let's look at the actual visual in Power BI. The KPI visual has three parts:

  1. Indicator – This is the actual value. You could think of this as the progress to date.
  2. Trend axis – this is a time period, eg day or month.
  3. Target goals - the indicator goal you are measuring against. The Indicator and Target goals must be the same, ie both must be currency, or both must be patients recovered. You cannot have a target goal expressed in monetary value and the indicator expressed as number of patients.  

This is an example of the KPI visual measuring daily output. Output could be anything from widgets to surgical operations, it is just designed to illustrate the point.

What does the visual show? Several things:

  • On the last day of data - 20th July 2020 - the output was 271.
  • On the last day of data - 20th July 2020 - output exceeded the goal by 8.4%. In this visual, the goal is not shown, but we could choose to display it. 
  • The KPI visual is in green, with a tick, showing that on the 20th July, we are on target.

A common problem is that your data doesn’t include a target. For example, you may be able to link to a database that stores data about output, but it does not include a goal. That’s not a problem for Power BI because you can enter goal data in an Excel spreadsheet and link the two sets of data together.

Is this the only way to show a KPI in Power BI?

No – and it isn’t always the best way depending on your requirements. The following illustration compares the KPI visual with a bar chart, a line chart, and a table with conditional formatting. All visuals are showing the same data.

If you were Production Manager, which way would you like to have this data presented? Which is the least informative?

If you would like to learn more about monitoring KPIs using Power BI in your business, do get in touch.

Friday, 15 May 2020

A Date with Microsoft Power BI - Part 2

This blog post is about how to create a date table in Microsoft Power BI. In the last blog post, we looked at what happens when you import data with a date column into Power BI. We identified a few limitations when you don't change the defaults. If you’ve not read it, take a look.

So why do you need a separate date table? Here’s two reasons:
  • To report on time periods that are different from the standard ones
  • To report on time periods that span more than one year.

There are several ways of creating a date table:
  • Import it – from a database or data warehouse or another service
  • Create it using M
  • Create it using DAX
To create a new table with a single column of type date/time using DAX:
  • In Power BI Desktop, select Modeling.
  • From the Calculations Group, click New Table. The DAX panel appears.
  • Rename the table name to MyCalendar and to the right of the equals sign type:
    • MyCalendar = CALENDAR (DATE(2020,01,01), DATE(2020,12,31))
  • Click the tick symbol to create the table.
This creates a single column date table from the first date to the second date. The syntax is:

CALENDAR(<start_date>, <end_date>)

The start_date and end_date must be valid date values, so use the DATE function with the syntax:

DATE(<year>, <month>, <day>)

Alternatively, you can use a value from your data, such as:

MyCalendar = CALENDAR(MIN(Sale[OrderDate]), MAX(Sale[OrderDate]))

The important thing is that the date table contains the full range of dates in your data, with no gaps.

There is an alternative DAX function - CALENDARAUTO() which scans your data, and finds the first and last dates and creates an appropriate date table. This has one optional parameter – fiscal_year_end_month.


If your tax year ends in March, for example, the DAX is:

MyAutoCalendar = CALENDARAUTO(3)

This creates a table with a single column called date, with the first date being the 1st April of the earliest year in your data, and the last date being the 31st March of the last year in your data.
Just two things to remember:
  • In Options, remove the Auto date/time selection
  • In Table Tools, select Mark as data table. Then select the date column. A warning is given to tell you that the hidden date table will be removed. Click OK.

Now that you have a table with dates in it, you can add columns to suit your reporting purposes.

To create a new column for month name:

MonthName = FORMAT(Sale[OrderDate], “MMMM”)

To create a new column for the month number:

MonthNo = MONTH(Sale[OrderDate])

To create a new column for year number:

OrderYear = YEAR(Sale[OrderDate])

To create a new column that combines the year and month values:

YearMonth = Sale[OrderYear] & "-" & Sale[OrderMonth]

Your calendar table is now beginning to take shape, and you can now create slicers containing months from specific years.  This is progress - but there's a lot more to working with dates in Power BI.

In the meantime, leave a comment to let me know how it's going. And if you want some help with your Power BI reporting or data modeling, then get in touch.

Monday, 11 May 2020

A Date with Power BI - Part 1

Are you creating your first report in Power BI? Or are you a bit more experienced, but finding that things are not working as you expected? Most business data contain dates. That means that most data analysis involves handling dates in both visualizations and calculations.

Getting to know how Power BI handles dates is well worthwhile.

For example, sales data might include the date that something was sold, plus when it was despatched, and maybe when payment was received. Training data will include the date someone books a training course, as well as the date they attend.

The passing of time and the events that happen in time are a fundamental building block to the way we analyse things.

Below is a simplified sales data table imported into Power BI. Count of OrderID provides the volume of sales, and it is analysed by date. To create a line chart:
  • From the Fields panel, drag OrderID to the Values area on the Visualizations panel. Check the aggregation is Count.
  • In the Fields panel, expand the date hierarchy
  • From the Fields panel, drag Month to the Axis area on the Visualizations panel. 

The line chart shows the volume of sales by month. 

Alternatively, delete Month from the Axis area and drag Date hierarchy in its place, you can see that now a drill down symbol has been added to your chart. Use it to drill up and down the date hierarchy, such as from Year to Quarter, from Quarter to Month, and from Month to day level.

But what if you do not want a date hierarchy? What if you want the date that was in your original data? Right click on the date filed to toggle between date hierarchy and date.

But let us just stop a moment. When you imported your data, did it include a date hierarchy? Mine just had a simple date, with no hierarchy. So where did it come from? At this stage you may say “I’m happy with my report, I don’t care where it came from!” Which is fair enough, but let me ask you some questions:
  • Does your company’s financial months and quarters align with those provided by Power BI?
  • Do you ever need to analyse data by week?
  • Do you use a completely different time period to analyse data, such as a sales period?
  • If you drag “Month” to a date slicer, which month is it referring to?
  • What if your data had included two dates, and not just one? How should that be handled?
These are not trick questions, honestly! But they are issues that emerge once you start building real reports for real decision-makers, and once your data starts to become more complicated.

We did not do anything to the date field to create a date hierarchy. So how did Power BI separate the month, quarter, and year from the date? The answer is a clue to the data model we need, rather than the data model we have.

Spoiler alert! There is a difference between what you see, and what is actually happening.

What you see is Power BI adding a date hierarchy, presumably because it understands dates. What is actually happening is that Power BI has created a completely new and hidden table.

To check that there really is a table, use Dax Studio to have a look. Below is a screen picture of the Power BI generated date table with the new fields: Day, Month, MonthNo, Quarter, QuarterNo, and Year.

There are a few important issues arising from this:
  • To create a chart with increments other than days, Power BI created a new, hidden table.
  • The new table had separate fields for day, month, quarter and year. Plus, month number and year number.
  • The table is hidden, and you cannot change it.
The functionality is provided by a setting in Options, under Time intelligence called “Auto date/time for new files”. When Power BI is installed it is Selected by default. So if you don’t want this functionality go to:
  • File
  • Options & Settings
  • Options
  • Time Intelligence
  • Deselect Auto date/time for new files

Next time I will look at how to create your own date table using DAX. This is a more flexible approach, and answers at least some of the questions raised earlier.

In the meantime, leave a comment or ask a question in the comments section. Or if you want some immediate help with your Power BI development, get in touch.

Wednesday, 29 April 2020

Accurate Record Keeping

Never has data been so much in the news. Whilst the numbers tell us nothing of the heartache behind them, they do tell scientists how best to handle Coronavirus and how to minimize its impact. We have all become familiar with R0 values, curves, and moving averages. Software like Microsoft Power BI is being used to analyse and visualize the data, and to make it more understandable.

At the same time, the importance of accurate record keeping has become more apparent. As the Head of Mortality Analysis at the Office of National Statistics (ONS) points out, counting the number of Covid-19 deaths may be important, but it is not simple. Issues such as the speed with which data can be recorded and disseminated, accuracy and consistency of classification, and missing data all contribute to how trustworthy the data is.

These issues are important when lives depend on the decisions that are made on our behalf.

Thankfully in business we are not often dealing with life and death. But we still need accurate data. Getting a good understanding of how many people have been trained in specific courses, by role and by date, for example. Or understanding how many meetings with clients by industry type, and outcome. These are not complex ideas, but getting the data is not always straightforward.

The issues with health data and business data are the same in how the data is stored. Both count something and then analyse it by a number of attributes. In the business intelligence terms, these are known as facts and dimensions. Facts are the things you count, and dimensions are how you analyse the numbers. The number of cases is a fact, and age group is a dimension. There are normally many dimensions for each fact.

As more businesses are working to become more data led, and less opinion led, keeping accurate data becomes vital. If you are making decisions based on inaccurate data, your decisions could be less than optimal, or just plain wrong.

The good news is that data has never been easier to manage, analyse and visualize. Microsoft Power BI is being used extensively to help manage the Coronavirus pandemic, and to help businesses make better decisions. It is an inexpensive piece of software, very powerful, and encourages sharing of data.

If you want to move from an opinion led business to a data led business, get in touch.  If you want help getting your data into shape so you can analyse it, we offer consultancy and development on the Microsoft Data Platform, including Power BI, SQL Server and Excel.