Power BI

Discover insights hidden in your data with Microsoft Power BI visualisations and create a data-driven culture with business intelligence for all

Unify data to create interactive, immersive dashboards and reports that provide actionable insights and drive business results.

Power BI uncover insights

Uncover Key Insights

Connect to any cloud or on-premise data source to find the best insights and answer crucial business questions.

Power BI data connectors

Use Data Connectors

Directly integrated with Dynamics 365, PowerApps, Microsoft Teams & Power Automate + prebuilt content packs.

Power BI empowereveryone

Empower Everyone

Bridge the gap between data and decision-making with access to the real-time metrics that matter most.

By connecting to Power BI, your data is immediately transformed into rich visuals to reveal insights and spot trends. Use our managed service to learn how to create Power BI reports, or instruct our consultants to create custom visualisations that will bridge the gap between data and decision-making.

Demonstrating Power BI

Demonstrating how Power BI connects with Microsoft Dynamics 365 to transform business reporting through powerful visualisations and data insights. In this recorded webinar, see what's new, and find out how Power BI will help you boost productivity and experience your CRM data in new ways. 📊 📈 💡

Video Transcript

open transcript close transcript

Power BI is an incredibly useful tool that is part of Microsoft’s stack which allows you to pull in data from various ar…

Power BI is an incredibly useful tool that is part of Microsoft’s stack which allows you to pull in data from various areas to create complex reporting for your business needs.

Some of those areas might include items like Dynamics, Excel sheets, Google Analytics or SQL databases. Some of the typical requirements of a visualization might include seeing which products that you're selling the most, at which location and by which user.

I’m going to run through some dashboards and then look at some of the different visualizations that are available to us. How to embed these dashboards into Dynamics, as well as do some very basic report creation in second half.

In front of us we've got a Power BI dashboard. This is made up of two different data sources. At the top we've got a sales one and at the bottom we're looking at service. This is used primarily as a kind of managerial tool to have a snapshot of information so a user can very quickly go in see exactly what they need to see, and then go into different bits. If they need to see more information, they can click on a visualization like this one and now takes me to the underlying data, or the underlying report.

In this report, we're talking about sales so you've got your actual sales versus estimated sales. At moment we've got a filter on so this looks at the actual sales across the different years. We've got 2018 and 2019 data here. If I click on 2018, it'll filter out two filter visualizations so the highest value accounts so Account-40 is the highest for 2018.

If I go to into 2019, the Account-60 is the highest. You'll also notice that at the end of the visualization for the actual sales, we've got a forecast.

Forecasting is quite a useful tool, Power BI goes back to look at your pre-existing data and then extrapolates it out and goes into the future to predicts what you're going to be using it for based on various factors.

So here if the data finishes in December 2019, looking at January it thinks that the forecast is going to be about £530k, with an upper band of £670k and lower band of £380k.

Some of other bits you can do in here is you can specify a specific sales user. Here we have our sales users at the bottom. We've got people like Fiona Foster, Peter Parsons and Steve Smith so if we click on Fiona it will filter out the rest of the data based on the opportunities that are associated with Fiona. Likewise, if you go into Peter it changes again and then if you want to see Peters 2019 data you can filter that as well.

If you go onto one of the other pages, we've got things like lead velocity so this is how long it's going to take for a lead to basically go through your entire sales process from creation to qualification and then the creation of the opportunity to the win of the opportunity.

For people like Mark up here, it takes them on average 26 days to qualify a lead but then only four days to convert that qualified lead into a won opportunity. Whereas someone like Terry it is the other way round. It will take them only nine days to qualify the lead but on average 57 days to complete the opportunity as won.

Again, we've got filters at the bottom so we can see the 2018 or the 2019 data. If you take that off data across all times. So, I’ll go back into 2019 and at the bottom we've done the same thing to show the lead velocity by business source this time. This is where the leads coming from so if it’s from LinkedIn eBay or partner referral you can see how long it's going to take to qualify the lead, then win the opportunity.

On the right hand side we've got a comparison between your estimated closed date versus the actual closed date by user and then by business source. You can see that Mark here tends to close down opportunities around 12 days quicker than he estimated he was going to.

On the next page here we have got one of the most interesting visualizations. On the right side is our key influencers metric. This is a fairly new visualization available to Power BI and it can do some pretty advanced data analysis based on the data that you're pulling in.

For now, we want to see what influences opportunities to be won. So basically looking at all the different factors that we've added to this visualization, which ones are causing opportunities to be won more often. We've added in things like ‘Does this account have a dedicated Account Manager?’, ‘Are there multiple service providers for this Account?’, we’ve added in the different business sources or lead sources, as well as the time to qualify leads.

If we click we can see that if there is a dedicated account manager you are 1.8 times more likely to win an opportunity than not. With the no it's only about 40% are won.

Likewise, if you click on multiple services provider as ‘yes’, there's a much higher chance of winning if you've got multiple services. For this you can put in things like ‘Who's the sales person?’, ‘How long they've been a customer’, ‘How many other opportunities have you won’, loads of different factors and allows you to really understand which metrics are causing your opportunities to be won or lost.

Then on the left hand side we've got your business source rate. The different ways the leads are coming in and how likely you are to win.

For an existing customer here, we've got an 82% likelihood of winning versus an 18% chance of losing. Things like partner referral it's about 50/50 and some of the other ones are a little bit higher or lower depending on the source.

Here you've got filters at the top so again I can see the 2018 data and then if we run the analysis for those, it gives you slightly different, or you've got the 2019 data again. We can run the analysis and again it’s slightly different.

You can also do this by sales person. If you look at the users we were talking about earlier if you go to someone like Mark Markham, you see for existing customers he's 91% won, versus 9% lost. If there is a dedicated Account Manager then 1.3 times more likely to win than if there isn’t a dedicated Account Manager.

Finally, we’ve got sales territories. We will look at all of the opportunities that have been won on a map on the right-hand side.

If I want to see if the opportunity was created in say 2019, it’ll filter it down so we can see which opportunities have been won at which location. Then you've got on the left hand side some fish swimming around in a tank. If I hover your mouse over you can see that Mark Markham has done £1.1 million worth of sales whereas Chris Collins has done £1.7 million. Naturally because we're talking about a sales team these fish have to be sharks!

That’s a couple of different visualizations you've got.

If we head back into the dashboard that's the sales one and then for service if we click on that it will take you into the service dashboard. Again, very similar but this one's more focused about service and cases. So ‘average case age’, ‘what's your oldest case?’, ‘where are the cases?’, ‘How many do you have open at the moment’.

Say here, you've got target of having less than 200 cases they've only got 160 and open a moment which is perfect. Also, top account usage so you can see which accounts are using your services more than others and again you can split that up by user again so we look at Fiona or again Mark who has those. If I go to say ‘cases over time’, one of the common metrics we find is that we have customers who want to see ‘how many cases did we have open at a specific point in time?’.

In Dynamics 365 it doesn't allow you to do that because you talk about live data. What we need to do is go back in time take a snapshot at that time and say we have got X amount of cases open at that time. One of the things that we can do for you is we can look at the cases that were created and the cases that were closed, do some wizardry, in essence, and then give you a number. Here we've got October 2019, there were 400 cases open with this system. On the bottom you've got a distribution of when the case was logged in terms of time and day of the month.

At the end you've got another fairly similar one to the sales but this one's talking about ‘where are the cases?’ and ‘What are the priorities?’ so we can just look at the p2 cases, you've got three investigating and you 2 in UAT, 2 waiting for details and then around the country.

Those are some of the visualisations you can see in Power BI.

If we move on to Dynamics we've got our demo tenant here. You might want to have your users keep going into Dynamics for all of their information including that of reporting. For that what we can do is we can use dashboard functionality to embed the Power BI dashboards within Dynamics. That's fairly straightforward. You can do new, then you've got a Power BI dashboard. You can select the one. I've already created one so here we've got the demo dashboard which will be the same dashboard you saw in Power BI but here it's all in Dynamics nice and easy to see.

Say if I want to go in and see more information on it, I can click it opens up into slightly larger page. Then again if I go to open Power BI report that will open the report within your Dynamics instance. So we have some users that never need to go anywhere else apart from Dynamics.

One of the other things you can do with a dashboard is you can have a combined dashboard. This is where you have Dynamics data like lists or charts alongside Power BI visualizations. If you do have data that's from different sources, or you have a dashboard requirement of a visualization that's too complex for Dynamics you can build in Power BI and then put it into Dynamics. Then have it on the same dashboard with the other tiles.

Moving on from that I thought I'd run through some of the ways that Preact can assist your business in building these reports and then we'll talk a bit more about creating some of the visualizations.

Preact can help in one of three ways. We can either provide you with training on how to build a report yourself, or we can do a scope where we can run through the requirements with you and then we build a report for you. Or, just somewhere in between where we can do the scope, run through the report, build a simplistic data set, as well as some basic visualizations, and then provide you training on how to take that data set and those organizations and then enhance them further for your specific needs.

If we're talking about the report we just looked. One of the visualizations was a line chart or sales over time. To produce this we've opened up a Power BI desktop and we've got the data set on the right-hand side and our visualizations in this section.

For this we'll need a line chart. I’ll click on that it’ll open up a blank and basic line chart where we've then got areas on the right-hand side for us to fill in data. We need axis, values and a legend. For this if we're talking about opportunities over time if I drop down the opportunities table I look at the value of the opportunity which will be the value of this visualization.

Then for the axis we'll be looking at the actual close date, so we've pulled that across. You can then see it has mapped this over time for you. At the moment this is looking at all of the all the opportunities but we may want to filter this down so we only see the won opportunities. For this you can see we've got a filters section. Within here if I drag and drop the status in I can then filter these by only looking at the won opportunities for that time period.

You can see it's filtered out anything that wasn't won that has irrelevant data in those fields. Now you can see values for 2012, 2013, 2014, 15 16, 17 but maybe you want a bit more granularity to your data. Maybe you want to see it by month, or by day, so you've got some controls up in the left-hand side where we've got a expand all down one level in the hierarchy, expand just one level entirely. A hierarchy in this instance is talking about year, quarter, month, day which we see on the right hand side.

If I expand all down one level in this it will then go to the different quarters of those years so 2013 quarter one, quarter two quarter three… A lot of the times this isn't particularly necessary for reporting so if you just want to see it by months we can go across to here we can get rid of the quarter get rid of the day, do the same thing again and then you can see that for the specific month of July 2014 the value was 525k and then it peaks and troughs as you go through the years.

You might add a few more analytics to this so on the right hand side we've got a little analytics tab here on here. I might want to see a trend line so if I just add in a trend there you can see trending of time going upwards in a positive direction.

As I mentioned one of the other interesting features is allowing a forecast. If I go down to the forecast on the right-hand side here add a new one and then say I want to forecast for the next three months we can select ‘three months’ and apply. It will then look at the previous data and then extrapolate out the next three-months worth.

With that there's a confidence interval. The more confident the model is the wider this is going to be. So if I drop that down to 75% you will see that the upper and lowers shrink drastically. So it thinks that for the next one in 2020 it's going to be a forecast of 360k with an upper and a lower band as expected.

You might not want to see all of this data in one go so you might want to add one of the filters. These are known as slices in Power BI and if you click on that to open up as a visualization I can then add a slicer in as say the ‘actual closed date’ so I pull that in this will be a bit between slicer effectively so you can set the before and the end date. It's very granular on a day-by-day basis which isn't particularly easy for a user to select the date range they want. If I just drop this down into using the date hierarchy it switches into years. Say I only want to see 2016 to 2019 data, it's as easy as that.

If I make that a little bit smaller now and then on here if I shrink this down. One of the other visualizations we had up there was the ‘lead source effectiveness’ so whether they've won opportunities or lost opportunities…

For that we used a 100% bar chart so if I click on that it opens it up and again you've got this field picker access legend value, that kind of thing. For this one we'll be looking at the count of the opportunities so for that I’m just going to pull in he opportunity ID as the value. The axis is going to be the lead source, so I’ll pull that down here. For the different colors we're going to want the status of it effectively. So if we go back into opportunities we can then pull in the status.

You can see here we have got lost and won opportunities.

At the moment, this isn't particularly readable and lost is currently a green which isn't synonymous with a negative aspect so if we go back onto the formatter and then look at the data colours we can change these. So lost can go into a red and then won can go back into green.

You might also want to see the actual values of it. If you click on data labels, it’ll then show you the labels. These are quite small at the moment in a way that some people might not be able to read this so you can just go in to up the text size a little bit you got to do that on the x and the y-axis just to ensure that everyone can read this as required.

Another of the other very useful things about Power BI is you're not limited to the visualizations that are shown in here. There is a really useful AppSource where you can go in and there's a vast array of completely free visualisation to download. One of them which is quite useful is the infographic designer. I’ll import this into the report and then we've got it down here. If I added a new page quickly and now I'll do the same visualization as I did the first time, we'll just make this as big as possible. Then I want to see a ‘sales over time’ so time I'm going to click and drag in and then it'll automatically resolve into value over time, and this would be a bar chart.

But a bar chart can be a little bit boring to look at so you can spice ease up a little bit by changing the logos of them effectively. In here we have a designer and I want to change the shape I can change the shape to be either a star which if I keep the ratio and multiple units stars or if it's something specific like a house, we can add it in the house. Again they're stretched a little bit so if I click on multiple units you can then show the multiple units. These aren't limited to just items that are part of Power BI, I could if I wanted to insert an image and upload it so if I upload this Preact logo and then I want to use and switch onto multiple units.

You can then use your company logos or even for salespeople you can put the faces in! Tthere's a lot of flexibility to customize the report to be for your business. That is a whirlwind tour of Power BI and I'm gonna open the floor to any questions…

Sales Analytics

To understand and improve sales performance, Dynamics 365 analytics include:

  • Top opportunities/salespeople per quarter
  • Sales cycle duration
  • Lead and opportunity conversion rates
  • Overdue opportunities
  • Opportunities won/lost per month
  • New opportunities per week
  • Marketing campaign effectiveness, lead generation rates
  • Sales pipeline and forecast reports by: month, quarter, year, salesperson, region, product & sales stage
Power BI Sales Dashboard

Customer Service Analytics

Service dashboards and reports track trends including spikes and dips in service calls and complaints to measure teams against KPIs including:

  • Customer satisfaction rates
  • Case resolution times and average days open
  • Open and overdue activities by owner, team, type, priority, account
  • Service activities created in the current month vs last month
  • Performance versus Service Level Agreement benchmark
  • Contracts nearing expiry
Pbi serv

Marketing Analytics

CRM marketing dashboards provide marketers with the insight to measure campaigns, track marketing spending and assess customer value. This includes:

  • Overall campaign effectiveness and ROI
  • Campaign cost analysis and cost variance
  • Most profitable/responsive market segments
  • Number and value of leads/opportunities by campaign
  • New leads per week/month
  • Lead conversion per month
  • Leads by owner, source, campaign, channel

Extended email marketing reports are accessible when Dynamics 365 is integrated with ClickDimensions.

Power BI Licensing

Is Power BI included in Dynamics 365?

Power BI is not included in Dynamics 365 but can be signed up for separately and integrated with Dynamics easily. This is available as a free solution but does not allow report and dashboard sharing amongst teams.

Power BI Pro is priced at £7.50 per user / month, providing 10GB of storage, 1GB report capacity, collaboration tools and full integration with Microsoft applications including Dynamics 365, Office 365 Groups and Teams.

Power BI Premium is available @ £3766 per month / dedicated cloud compute for enterprise requirements enabling organisations to make use of a dedicated capacity of 50 GB for large datasets and export reports into an app for sharing with any user.

Find out more

Connect CRM and other data sources with Power BI to quickly gain new data insights. Contact us to learn more about how Power BI can help your business embrace a data culture where new insights are uncovered each day.

What our clients say...

Microsoft Power Platform

Power BI is part of the Power Platform, a single low-code platform that spans Dynamics 365, Office apps, Azure and standalone apps - both cloud and on-premise

Read More