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.

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.

CALENDARAUTO([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.

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.

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.

Business as normal, but not as we know it

There will be many legacies from the Coronavirus pandemic, not least the adoption of technologies we have had for some time. Many companies have been using video conferencing successfully for years, but many more were flying their people around the globe for reasons that were plausible at the time. Now grounded, they are using video conferencing and will find that travel budgets are reduced once the health dangers are over.

Cometh the pandemic, cometh the ability to get to grips with Teams.

It is not just video conferencing; it is also cloud adoption. Cloud providers such as Microsoft Azure have been struggling to keep up with the sudden surge in demand. The myriad of reasons why the cloud was bad for business have melted as home workers need the same information as they had in the office.

Do any of us think we will go back to life as it was two month’s ago? No. It is impossible to unlearn a new skill, and it is impossible to become less efficient once we know how to be more efficient. So wider spread use of technology to do business will become the new normal. Which is good news for some, but for others their hearts will sink.

The harsh reality is that we cannot cherry pick. The rapid demise of the high street has demonstrated that all too graphically. If we want to flourish, we must understand how to offer value wherever and however people want to buy. Whether that is on their mobile, at their desk, or in store.

Value is where the internet scores. On the internet we can flick from product to product, compare prices and companies quickly and easily. We may want the cheapest price, or we may want specialist advice; value is not the same for everyone. Nor is it the same at every step in the customer journey.

The difference between online and physical stores is that we can measure perceived value, as well as sales. If I read an article, and return to it, there is an indication I am getting value from it. If the article gets very few readers, it indicates a lack of value for readers. Diligent measurement, analysis, and tests enables us to understand where the value lies for our customers.

The shift to online will bring many benefits, not least a cleaner planet. But after social distancing ends, we may well find it more convenient to get advice online rather than face to face. We may prefer to video call rather than pick up a new infection in the doctor’s surgery (and the doctor may feel the same). We may find it easier to chat to our hairdresser about the best shampoo to use, rather than go into the salon. And there will be many other areas online were value can be found for businesses willing to provide it.

Behind all these opportunities, and very many more, will be measurement. Knowing for sure where people find value, and where they do not. Which is where Microsoft Power BI scores so highly. You do not have to be Amazon or eBay to use analysis and AI technologies, they are available to businesses of all sizes. Measurement, analysis, and better understanding are available to everyone.

Pandemics are not generally the best way to adopt a new technology, but the lesson is that one way or another there is no way to hold back progress.

As always, if there is anything in this blog you found interesting, valuable, or you would like to argue with, then get in touch. We are Microsoft Business Intelligence specialists, and Gold Partners for the Microsoft Data Platform and if you are grappling with a data problem, we might be able to help.

Achieving Goals with Microsoft Power BI

Goals are an important part of business; they provide focus, direction, and motivation. Good goals can inspire us to perform better than our best – what Jim Collins calls Big Hairy Audacious Goals (BHAGs). But there is a difference between setting a goal and achieving it. Otherwise we would all be slim and complaining about the worldwide shortage of yachts.

Microsoft Power BI can help you achieve the goals you set. How? Research by Deloitte  suggests there are three principles you should follow with goals:

1. Goal clarity
2. Ongoing review
3. Making goals matter

Goal clarity

Goal clarity comes from defining how the goal will be measured.

By setting a goal and defining the measurement criteria at the same time, including which system the data will come from, you remove any discussion about what the goal means.

What often happens is that we set a seemingly simple goal, let’s say sales per month, without defining the data that will be used because it seems obvious. A sale is a sale, isn’t it? Three months into the year, and discussions start. Is it:

1. When the Sales department get agreement from the customer to proceed?
2. When the contract has been signed?
3. When the deposit has been paid?
4. When some of the work has been invoiced?
5. When all of the work has been invoiced?
6. When all of the work has been paid for?
7. Including commission/charges/carriage? 

Unless the measurement of the goal is clearly defined, debates and discussion will follow. Or worse, some people will work to one set of rules, and others will work to another set of rules.

Ongoing review

The Deloitte research also found:

“Organizations that have employees revise or review their goals quarterly or more frequently were three-and-a-half times more likely to score in the top 25 percent of business outcomes.”

With clear measurement criteria in place, Power BI makes it possible to review as frequently as required. Power BI has a range of options to review progress to targets, from tables to cards, gauges to KPI indicators.

What’s important is that once the report is linked to the data it can be regularly refreshed, so everyone is working on current information.

Make goals matter

Power BI helps you clarify your goals, and review progress. It also helps with setting goals that matter to everyone, by allowing employees to set and monitor they own goals.

Goal setting power is in everyone’s capability with Power BI due to the range of data sources it works with, including Excel and Access databases. In addition, it gives everyone the ability to create powerful visuals that compare actuals to targets.

As most of us have repeatedly found, it’s not setting the goal that’s difficult, it’s tracking progress and keeping it visible that’s hard.

Power BI is a game changer when it comes to goals, targets, key performance indicators (KPIS). Far from “just” being able to visualize progress, it helps us clarify how we will measure our goals and track our progress towards them. And it gives us the ability to share that progress with others.
And for the first time, this isn’t something that happens within IT – everyone has the ability to create their own hard-hitting reports that show progress towards important goals.

If you’d like to find out more about using Power BI to track your goals, get in touch to find out more.

Bersin by Deloitte - Effective Employee Goal Management is Linked to Strong Business Outcomes 

Power BI Dashboards

Power BI Dashboards are particularly powerful reports that have more to them than meets the eye. In this blog post, I look a bit more closely at Microsoft’s own definition of a Power BI dashboard. This is what they say:

A Power BI dashboard is a single page, often called a canvas, that tells a story through visualizations.

Intuitively, we understand what a dashboard is – we’ve all got them in our cars. But what’s a Power BI dashboard? Let’s unpick Microsoft’s definition …

Power BI dashboard 

A dashboard isn’t a regular report. Power BI dashboards are created using reports that have already been created. Power BI reports are created using data from one or more systems. So, that means that a Power BI dashboard is made up of elements from one or more reports, perhaps from different parts of the organization, using data from a number of different sources.

The dashboard is updated when the underlying report is updated, so you always have the latest information. That’s powerful! We might be used to our car dashboards always being up to date, but sadly it’s not always true of our business reports.

Single Page

The analogy with a car dashboard only goes so far. Modern cars do allow you to change the view on the dashboard, which you could argue is like changing the page. A Power BI dashboard, however, is limited to a single page. That’s not for a technical reason, rather it’s to limit the data and visuals you can add. By constraining the dashboard to a single page, you are forced to choose only those visuals that are important and necessary.

As anyone who has ever written an academic essay knows, when there is a limit, somehow the final version is better for it!

Canvas

Canvases are normally found in artists’ studios, waiting for a masterpiece to be painted. The Power BI canvas is also blank – waiting for a picture of sorts. In contrast to the checked page of Excel, Power BI starts with a white canvas. It doesn’t suggest lists of numbers, rather it encourages visuals to be created and positioned for the greatest impact.

If you’ve ever tried to create a dashboard using a spreadsheet, you will know how difficult it is to design something that looks good. The Power BI blank canvas, on the other hand, is easy to align, position, and move visuals to where they make the most sense.

Tells a Story

Story telling has become fashionable business notion, even though most CEOs are more comfortable with numbers and charts.

However, stories do have a beginning, a middle, and an end, and that’s a useful idea when it comes to designing dashboards. The beginning is where we’ve come from. The middle is where we are now, and sometimes why we are where we are. And the end is what we want to achieve, or the target.

To tell the story we can use comparisons to last year, last quarter, etc. Or comparisons to target, or a key performance indicator. Although the CEO might not recognise it as a story, it gives a logical flow and sense to the data.

Visualization

Visuals make data easier and faster to understand. They are great at comparisons – large blocks and small blocks are recognised by the brain a lot faster than a table of figures.

That’s not to say that everything should be shown on a chart or a pie chart. When the number is important, prominence and size might win out over fancy and visual. Headline performance indicators such as revenue or profit for the period might be simply stated – big and bold.

Visuals can clarify and enhance understanding, but if they don’t think about simplifying and highlighting only what’s important.

So that’s a Power BI dashboard, from the horse’s mouth, as it were. Power BI dashboards have power and punch, and can be used by departments and businesses to keep up to date with important data.

If you’d like to find out how you could be using Power BI dashboards within your organisation, get in touch.

What Drives your Decisions?

One of my favourite quotes comes from Jim Barksdale, the CEO of Netscape and COO of FedEx. He is credited with saying:

“If we have data, let’s look at data. If all we have are opinions, let’s go with mine.”

But having data isn’t enough – most businesses have more data than they can analyse. Data must be presented in such a way that it communicates something useful and actionable. In other words, you need to turn raw data into something the business can use.

Raw data often has inconsistencies, errors, and different ways of reporting the same thing. Power BI allows you to clean up these problems and manipulate the data into something you can work with. It also allows you to create a data model that will drive analysis and visuals. In other words, it has all the business intelligence features you need to make informed decisions.

It has several game-changing features such as:

• Being able to link to many different data sources.
• Being able to manipulate and clean the data.
• Presenting data in new and compelling ways.
• Creating dashboards and scorecards, including key performance indicators (KPIs).
• Being able to share reports and dashboards securely.

This means you can track specific business indicators, updated by one or more than one data source. You can create reports that use more than one data source. And you can create powerful dashboard-type reports that focus attention on specific areas. All updated automatically when the underlying data is updated. It’s powerful – as the name suggests.

Business intelligence was once out of the reach of departments and mid-sized businesses. Budgets were large and project lead times were long. Microsoft Power BI is the next generation of business intelligence and it has changed the way we work, and how we make decisions.

It’s easy to get started; you can download Power BI Desktop for free. If you need some help, we have end to end experience from cleaning data, creating data models, and advising on impactful reports. And we can train you in all aspects of Power BI.

So, if you are a department that wants to bring data together from more than one system, track specific indictors, or present data in a way that can’t be ignored, get in touch.

6 Reasons to Rethink Your Business Reporting

Well-designed business reports can support business strategy, form the backbone of decision-making, and guide your every action. Unfortunately, many businesses are working with reports that do a lot less.

Legacy systems often produce lists of figures that send us to sleep, rather than prompt action. Which is a shame, because regardless of how or where your data is held, modern, visual reporting is now available to everyone. Unfortunately, most business data is never analysed or used to make data-driven decisions, even though data analysis has become a competitive issue.

So, are your management reports driving your business forward, or holding it back? Here are 6 reasons to rethink your business reporting.

1.     Support your strategy. Implementing strategy is tough. Conflicting views and too many distractions make it easy to get blown off course. Clear reporting that shows the status of your key business goals improves the chances of implementing your plans.

2.     Visuals are better. Visuals have two big advantages over lists of figures: speed and impact. When data is shown visually, it’s much easier to understand. Our brains can process variances, comparisons and trends within a fraction of a second. And when data is shown visually it becomes compelling and much harder to ignore.

3.     Dashboards let you travel faster. Having data on a single dashboard has big benefits. Firstly, part of planning is deciding what’s important and what’s not. By designing a report on a single page, focus on the important goals is improved. Secondly, having a visual and single page view gives you an instant view of how well things are going. When you are driving, you don’t expect to have to examine a 16-page report to find out whether you are running out of fuel or the engine is overheating. Business dashboards give you the same at-a-glance view of your business journey – allowing you to travel safer and faster.

4.     Big picture to detail. Interactive reporting works in the same way as our brains, allowing the big picture to be understood before the detail. The ability to drill down allows us to understand the overall situation first, and then investigate interesting parts further. Whether things are going well or badly, big picture to detail allows us to work in a logical way.

5.     Access from anywhere. We may still do most of our work in the office but thinking happens anywhere and anytime. Reporting that can be viewed on a mobile or a tablet means you have your data with you, whether you are working from home, travelling, or suddenly get a flash of inspiration during breakfast.

6.     Better collaboration.  All of us work better when more brains are on the problem. Modern reporting encourages collaboration by sharing reports and allowing input.

Modern reporting software has much more ambitious goals than the listing reports of old. Highly visual, compelling reports that convey more understanding, more quickly. Thanks to software like Microsoft Power BI, it’s never been easier to create highly effective visual reports.  

If you want to rethink your business reporting, get in touch to find out how.