KPI’s in Power BI: Not as hard as you think

KPI compare

Power BI just keeps getting better. The addition of the KPI visual to the standard palette is just another example in a long line of improvements, and the subject of this quick article.

Key Performance Indicators have been around long before the computer age. Show of hands: Who has ever browsed the new car showroom and NOT looked at the window stickers listing the vehicles’ MPG ratings? I thought not. While maybe not a true KPI as explained by Gerke & Associates, Inc here, Miles per Gallon is something we all understand when talking about the performance metric of a car. Most cars now come with computerized displays that will show instantaneous MPG, or an average over time. Keep these in mind when we transition this discussion over to Power BI.

In SQL Server Analysis Server cubes, you had the ability to create KPI’s inside the cube. They could then be browsed by the tool to which it was connected, something such as an Excel Pivot Table. Thought slightly different, KPI’s were also available in Analysis Server Tabular Models and Excel PowerPivot, both precursors to the Power BI Desktop.

But data analysts and modelers may experience premature disappointment to find that there is no way to create a KPI when using the Power BI Desktop designer. Knowing it was there in Excel PowerPivot models doesn’t help. It was there before, why did they take it out? Enter the KPI VISUAL.

KPI on Palette

By selecting this visual, you can create a KPI out of any metric in your model. It has three simple fields in the designer to define its appearance: Indicator, Trend axis, and Target goals. Let’s take a look at each of these in turn.

  • Indicator: This is the aggregated column or measure being considered. It could be as simple as the Sum of Sales. You do NOT need to slice this metric down to the latest value, such as [Sum of Sales for the last full month] or anything crazy like that. The base metric will suffice. The reason is explained below.
  • Trend axis: Grab a date type field for this (obviously) and (not so obviously) select the granularity: year, month, etc. Doing so will tell the KPI visual how you want to aggregate the Indicator metric over time.

At this point, your KPI should display two things: a black number and a grey shaded area in the background. We’ll explain all this at the end.


  • Target goal: This can also be an aggregated column or measure similar to the Indicator. If you don’t have one, it’s easy to create a ‘static’ goal by adding a new Measure with a simple static value such as: “KPI Goal = 100″, which is what I did for this demo.

Now, there’s only two options when you get all those things set: either it looks right, or it doesn’t. Consider the following two KPI Visuals from Power BI, both created from the same set of data, both using the same field settings.

KPI compare

The green one on the left I can tell you is how it is supposed to look based on the data I entered. It’s easy to see in the 6 rows that there has been a steady increase over the last 4 years.

KPI Data

So why did my initial attempt at a KPI result in the red one on the right? Apparently there is a dependency on the order in which the KPI is designed. If you selected a metric to add to your canvas first, and then decided to switch it to a KPI, you may get erroneous results. If, however, you start by adding an empty KPI to the canvas and then populating the three fields, you will probably see what you expect. If it doesn’t look right, the fix is quite simple really: remove the Indicator field and re-add it! It may be quirky, but it works.

Now let’s talk about all the pieces of information contained in this one (now correctly formatted) KPI visual. Referring to the green version on the left above, the bold number 120 corresponds to the value calculated (probably summed) for the latest point of the Trend Axis. Based on my data, that is the point for 1/1/1015. Typically, data would be spread over may dates over the years, but the aggregation of the Indicator, and the granularity of the Trend axis will determine the latest point to be displayed. The shaded green area is the trend plot for the Indicator. This shows a decrease at the very beginning, but steady increase after that. Next we see the green check-mark and green shade, indicating that this point is above the goal. (Using the format menu for the KPI you can reverse this if a lower number is better.) Finally, the small black text below the Indicator shows us the Goal, and the distance from that goal.

With this type of control in your hands, you can easily create KPI’s that display the same Indicator, but for different time slices such as by year, quarter, month, week, or day, depending on your needs.

Applied Machine Learning: Optimizing Patient Care in Hospitals, Profitably

Health Care

Why are so many industries exploring Machine Learning as a means of delivering innovation and value?  In my view, the technology speaks to a primitive urge – machine learning is like having a crystal ball, telling you what will happen next.  For a business, it can convey information about a customer before they introduce themselves.  On a personal level, when I consider what I would like to have information about in advance, the first thing that springs to mind is obvious: my health.  Am I about to get sick?  How can I improve my wellness and overall health?  If you are wearing a Fit-Bit right now, then you probably agree with me.

In my last blog, I shared some real-world examples for how the Hospitality Industry applies Machine Learning.  What about Health Care and Hospitals?  While hospitals have similar challenges, in that they accommodate guests who stay overnight, the objectives in health care are quite different, and changing rapidly.   The Affordable Care Act is driving new business models, incentivizing outcome based reimbursement as opposed to volume based reimbursement.  Unlike hotels, today’s hospitals are interested in ensuring their guests do not have to return, at least not in the short term.   They also need to manage costs in a way they have not been incentivized to do in the past.  Hospitals across the country are considering how predictive analytics can have a  meaningful impact on operations, leading to improved patient health and improving the bottom line.

The cost savings opportunity for health care providers is startling. Here are just three examples:

Reducing Hospital Readmissions – in 2014, Medicare fined 2,610 hospitals $428 million for having high hospital readmission rates. Leaving actual fines aside, industry analysts estimate that the overall cost of preventable readmissions approaches $25 Billion annually. As a result, hospital systems all over the nation are mobilizing to intervene, using ML to identify risk factors which are highly predictive of readmission. Carolinas Healthcare System, partnering with Microsoft, did just that. Using data from 200,000 patient-discharge records, they created a predictive model deliver customized discharge planning, saving the hospital system hundreds of thousands of dollars annually. Read the article in Healthcare IT News.

Clinical Variation Management – Mercy Hospital is partnering with Ayasdi to find the optimal care path for common surgical procedures. Using knee replacement as an example, the Clinical Variation Management software helps hospital administrators find clusters of patient outcomes, then enables the exploration of those clusters in order to correlate a metric (i.e. Length of Stay) with a certain regiment or activity. Watch this video to learn how Mercy Hospital saved $50 million by applying Machine Learning to an extremely common procedure.

Improving Population Health – Dartmouth Hitchcock, a healthcare network affiliated with Dartmouth University, is piloting a remote monitoring system for patients requiring chronic care. 6,000+ patients are permitting the hospital to collect biometric data (i.e. blood pressure, temperature, etc.) in order that nurses and health coaches can monitor their vital signs, and machines can predict good days and bad days. Quite the opposite of hospitality: Dartmouth Hitchcock is trying to keep the guests from needing to checking in! Read more about the Case Study from Microsoft.

Are machines taking over for physicians? No. The Patient – Physician relationship remains (and I think will always remain) central to the delivery of personal health care. However, it seems clear that ACA is providing significant rewards to health care providers who manage population risk better. Machines can help here: through data, machine learning can find risk “hiding in the data”.

Contact Blum Shapiro Consulting to learn more about how Azure Machine Learning can curtail hospital readmissions, identify variations in common clinical procedures and improve patient population health.

The Importance of Microsoft’s Purchase of Xamarin

Dashboard on a TabletThe Xamarin purchase by Microsoft is important for developers, entrepreneurs, startups and companies of all sizes. In the last 5 to 10 years, the industry has drastically changed, starting with the introduction of mobile devices and continuing with the rise of “Internet of Things” (IOT). Xamarin addresses these industry trends by being a versatile tool that builds software on any OS and can be distributed to any platform. With this purchase, Microsoft gives the industry a clear signal: mobile is here to stay. The future of software development will be done on any OS using the .Net Framework and distributed to any platform or device.

What’s in it for Developers?
Microsoft mobile developers could not be happier to hear that the years of invested knowledge and study will continue to have marketable value in the industry. But, Microsoft must choose its next step wisely. Current prices for Xamarin Tools are out of touch with the expectations of home grown developers, who would rather build on tools like Cordova or on the specific OS platform, rather than pay an annual fee. Of course, the discussion on pricing is much more complex than simply pleasing developers expectations. Xamarin accomplished the impossible, but they also have to make money in-order to continue delivering a great set of Mobile Development tools. It is up to Microsoft to clear the path forward. If they do, mobile developers will be waiting (and chanting).

What’s in it for Startups?
Mobile App startups rely on LEAN and RAPID tools for developing their products. Tools need to be highly customizable, flexible and cannot limit creativity. One bad technology choice can mean the difference between success and failure. Xamarin is attractive to startups because it provides cross platform capabilities, which their competitors lack. Xamarin has a proven record of accomplishment, they have shown commitment, they are innovators and have demonstrated strong community engagement.

Conversely, Startups are attractive to Microsoft. Microsoft can leverage the Xamarin community to market their other services such as Cloud, since Startups are not just Mobile first they are also Cloud first. Having said that, it is important for Microsoft to note that startups do not waste time, once they determine the tool and platform that meets their needs, they don’t look back. Therefore, Microsoft needs to get this purchase and integration right from the beginning.

Why does Microsoft care about Entrepreneurs?
Entrepreneurs will always gravitate to the best tool, as long as the tool is of high quality, affordable and is on the bleeding edge of technology. This is why Xamarin is important for Entrepreneurs: over the last few years the Xamarin Tools have provided the latest version of each mobile OS right from beta to GA. Entrepreneurs need to stay on top of the technology without having to learn 5 different types of platforms, dev tools or programming languages. Xamarin’s level of commitment makes it the perfect tool for entrepreneurs. If you are a mobile app entrepreneur, you are most likely independent-minded, always working to build your empire, and looking for opportunities to change the world. Entrepreneurs are also the best salesmen: they work day and night to build the next generation of tools, products or companies and will continue to look at Xamarin’s commitment and expect Microsoft to honor it.

How about Companies?
The importance of this acquisition will vary greatly, depending upon a company’s current mobile strategy. First, there are the companies that already use Xamarin Tools and own MSDN Subscriptions. No doubt they are thrilled. Their investment is strengthened by this purchase, and they look forward to improved licensing and integration with Visual Studio. On the other hand, if you are a company that has stayed away from mobile, this would be the right time to revisit your mobile strategy. If your company does not have a strategy on how it would compete in future markets like Mobile and IoT devices, then you need to develop one. Not all mobile applications are for consumers. The largest majority are in-house apps that improve their day-to-day operations. These types of apps can make a huge difference on how your company responds to market changes and remains competitive. Just take a look at small mom and pop stores and notice that they are mostly using iPads to track customer purchases. Those iPad Apps help improve customer engagement by tracking traffic, personalizing orders, gathering feedback, sending receipts via email and lots more.

Microsoft’s future is now Xamarin’s future. Microsoft clearly means business, and it is time to adopt a Mobile strategy and immediately set your eye on the next wave of technologies such as IoT, Big Data, and Machine Learning. All of which are part of Microsoft’s platform and will now be implemented via the Xamarin Tools.

SharePoint 2016 Branding Revisit

With SharePoint 2016 looming in the horizon, it’s time to revisit some of the branding concepts in SharePoint. I describe SharePoint branding as the composite creation of an identity that expects an emotional reaction from its consumer. But wait, in SharePoint circle’s the word branding has been flagged as a dirty word, so therefore going forward I will be calling it “UI Style”. In my experience with SharePoint UI Style solutions, they are mostly composed of custom master pages, custom page layouts, tons of JavaScript, CSS and other UI assets. These UI Style solutions can bring state of the art site designs into SharePoint. However, they are custom and will require upkeep with each new version of SharePoint and in some cases completely re-built. So if custom UI Solutions are not future proof, what can you do about it? We will discuss this and other UI Style topics below.
SharePoint UI Style Evolution
First let’s take a look at the evolution of SharePoint UI Style from 2010 to 2016.
Starting in SharePoint 2010, we can see the UI Style evolution with the introduction of the Ribbon menu, in 2014 we were introduced to the Seattle theme which was focused on removing chrome from the UI and now in 2016 we see the introduction of the “App Launcher” aka the “Waffle”. At this point the industry has a fairly good understanding of SharePoint UI Style designs and solutions. Here are the basics, the SharePoint UI Style is built on top of master pages, which include the outer layer composed of the header and footer. This is where you put your company logo, banner images and where the navigation control is displayed in your site. The center or what I call the “White Space” is where page layouts are loaded. Page Layouts are your home page, news articles, blog post and others. Note: that the concept of master pages and page layouts has been around since Asp.Net 2.0 and is stable. All other elements are called Web Parts, which gives you functionality like viewing documents, calendar, news feed, photo gallery and much more. While the out-of-the-box UI Style provides basic look and feel, if you want to change it you can either extend it or customize it, so let’s take a look at what a SharePoint Custom UI Style solution is.
Custom UI Style Solutions
Custom UI style solutions are normally built using this designer tools like Dreamweaver, Visual Studio and SharePoint Designer. Normally a designer would generate all of the images, CSS and HTML pages and then a SharePoint developer will take them and incorporate them into a SharePoint Visual Studio Project. Developers would then build a Visual Studio Custom UI Style solution that generates a file with the extension of WSP or Windows SharePoint. This file contains a CAB file with all of the DLLs, XML, CSS, XLT, JavaScript, Aspx and HTML files. Below is a very simple SharePoint Visual Studio project that contains master pages, page layout, CSS and JavaScript files. It is important to note, that you can build a custom UI solution without Visual Studio, you can perform the same action from SharePoint designer and Dreamweaver. Having said that, for large and complex UI Style Solutions you will benefit from the Visual Studio version control, and intellisense.

SharePoint UI <a href=SharePoint UI Style Custom Solution

Master Pages
When deciding on building a custom UI Style solution it is important to understand the impact specially when design custom Master pages. So let’s take a look at the master pages from 2010 to 2016, notice that the master page is are referencing specific versions of SharePoint libraries. When a new version of SharePoint arrives, these references will need to be updated. Which means that it requires you to re-deploy your solution and retest your site. For a smaller SharePoint implementation this should be no issue, however for larger and complex UI Style solutions this can be an expensive endeavor. So think and plan carefully when developing custom UI Style solutions.

Master Page References to SharePoint Version
On the bright Side
If you determine that you must have a custom UI Style Solution, then I encourage you to contact us or search the MS Partner Network and select a Partner nearest you for assistance. Also you can purchase a prebuilt UI Style solutions online from sites like,, and many others. If you like do it yourself, then I suggest you start at it contains a simple to understand UI Style solution for SharePoint utilizing the latest bootstrap3 framework. I will warn you, these and most commercial pre-built UI Style Solution focus on Publishing Site and not Team Site.
Future Proof UI Style Solution
That’s right there is an alternative, a more future proof UI Style solution for SharePoint. It is nothing new, it has been recommended by Microsoft since SharePoint 2010 and with introduction off SharePoint 2016, it is even more important to a brace it. Let’s start with SharePoint as a service and not a platform. Choosing between the two can determine how future proof your solution is, if you decide to use SharePoint a service then you have full control of the UI Style design and implementation. Your site can be build using any Web technology including Asp.Net MVC, while SharePoint would provide all the services need to run by the site. When SharePoint is the platform, then you have less options for your UI Style solution. Having said that, recently Microsoft Pattern and Practices have release new tools to improve how you modify/extend the out of the box SharePoint UI Style. You can find more information on the tools at Below we will go over two examples how you can customize your SharePoint UI Style with no custom master pages or Visual Studio.
First let’s start with themes. Themes have been around SharePoint since 2010, they allow you to style elements without modifying master pages or page layouts. They get overlook by many developers but with patience and good CCS skills, you can create a theme that will operate in SharePoint with no custom development. Microsoft provides a SharePoint Color Palette Tool which allows you to set the background image, the font color, navigation background color, header footer and others elements. The tool is easy to use, and after you create your new theme you simply upload it into SharePoint and set your site to the new Theme as shown below. For more information on how to apply a new theme feel free to contact us.
SharePoint UI Style Theme
Modern Responsive UI
Modern web sites are now expected to support a variety of screen resolutions. Your SharePoint site is no different, it must have some level of support and one way is via Office PnP SharePoint UI Responsive solution. Recently Microsoft Pattern and Practice release a SP Responsive UI using a pure JavaScript Solution via there Pattern and Practice group. This SharePoint UI Responsive solution does not include any custom Master Pages or modified Page Layouts. It is simple JavaScript and CSS solution to Responsive UI solution prevents you from having conflicts when migrating to new version of SharePoint since it does not edit the Master Page. Another positive to this approach is that it works on Team Site while most Responsive solution focus on Publishing Sites.
You would enable the SharePoint UI Responsive solution into your Team Site using PowerShell and not Visual Studio as shown below.

The result is a Team Site which response to the screen resolution just as customers expect.

TeamSite SiteContent Responsive TeamSite Responsive
Team Site Responsive Site Content Responsive

This SharePoint UI Responsive solution is a great start for your development of a UI Style solution that does not have any dependency on the SharePoint infrastructure. With that and some create CSS design you can build any UI Style that means your need. Next step you would need to pick the right UI Style.

UI Style Conclusion
So far we talked about the evolution of SharePoint UI Style, how they are build and alternative future proof solutions. Now it is important to pick the right UI Style. Let’s first start by understanding what SharePoint means to you and your organization, what are the business use cases, and what is the organization strategy for the next 5 to 10 years. That’s right your organization vision and goals should drive the SharePoint UI Style and not the other way around. If your organization is an insurance company and you want to provide better care for your patience or if you are a none for profit organization and your SharePoint is focus on, bring awareness and driving donations. Each vision will result in a completely different UI Style with different implication for SharePoint. Perhaps one implementation displays the patient schedule, shows alerts, news announcements or videos. Or Perhaps the site, simply shares information on the latest donation events, and how visitors can make pledges. The bottom line is the right UI Style will help improve sales by keeping visitor engage when it clearing reflects your organization culture, vision and strategy. So how do you get there, start by reaching out to us and we will get you started on the right path.