​How to Gain Sales Insight with Power BI & Microsoft Dynamics


Microsoft Dynamics has numerous inbuilt sales dashboards and views which can be quickly customised to fit individual user needs.

These are highly flexible reporting across selling processes and showing the status of a sales pipeline.

But if you are relying on just CRM, or perhaps Microsoft Excel, to analyse your sales activity you are missing a trick!

That’s because Power BI offers significantly greater reporting power enabling teams and individuals to visualize performance, understand trends and discover valuable insight which can be acted upon.

Dynamics contains a wealth of sales data but when it comes to interpreting these analytics, Power BI offer unmatched reporting power.

By connecting Power BI to a service like Microsoft  Dynamics 365, it’s possible to create dashboards to easily visualise and interrogate data.

So what does Power BI offer that CRM doesn’t?

Let’s take a look…

First up, we’ll look at a Pipeline Report dashboard:


This packs in a lot of detail including the volume of opportunities created by month, a breakdown of opportunity values by customer type and the top opportunities values by account.

In this example, the dashboard can be filtered by region, country and product type to dynamically update these charts.

According to the Actual vs Target performance the sale performance is behind target so we need to look at converting some opportunities that are at an advanced stage in the selling process.


The opportunity value by pipeline phase reports that CRM opportunities at the ‘negotiation’ stage total $16m. From this graphic we can drill down to see this breakdown by individual CRM sales user:


This reveals that opportunities owned by Derek Brown account for $4m of this total.

But does this reflect a single opportunity, or multiple potential deals?

Using Power BI we can click down to another level to see that Derek has one potential $4m order at this stage for his client, Southridge Videos:


With this insight a sales director can take timely action by following up with Derek to work on the best strategy for getting this deal over the line, as well as converting further opportunities at the negotiation stage which are owned by other users.

Next, we’ll look at some Sales Quota / Target metrics.

In this example, the data reflects a global sales operation but this could easily depict country regions for a national sales team:


According to this dashboard the organisation has 7 sales managers behind target this financial year, with 10 managers who on target to meet or exceed their quota.

Data can be analysed on a regional basis so I'll click Europe which the bar chart confirms is one of several territories where performance is down on target:


This narrows the display by filtering the charts showing data for the 7 sales managers working in this region.

A sales director can easily visualise the positive and negative variances across individuals in this region.

The Trends dashboard represents a significant departure from the fairly flat nature of reporting within the CRM interface as this directly compares and overlays performance between two periods:


The revenue by month chart shows this year’s performance has consistently trailed the previous 12 month’s with notable variances in October and November. Again, with Power BI we can drill down to better analyse performance on a monthly basis and understand the cause of these variances.

Using CRM sales opportunity data we can conclude that successfully opportunities are taking an average of 6-8 weeks to convert while more opportunities are likely to drop out after 5 weeks. Why is this, and what stage are these opportunities being lost at? Using Power BI these pain points can be more easily identified and answered.

Looking at the account revenue list, Power BI users can see how the top customers rank this quarter, or year, and how this compares to an earlier period to understand which accounts are in growth, and which ones are slipping down the revenue leader-board.

Finally, the Sales Performance dashboard enables sales leaders to analyse their profit margins:


In this example the headline figure shows the organisation is slightly down on its 16% target margin for the year to date.

However, this masks some significant sales variances across several countries. Sales in Spain and France are outperforming expectations yet performance in 5 countries is falling well short of target with sales in Germany especially poor.

This dashboard also highlights discount margins by product category which reports Electronics orders are attracting proportionally greater discounts than any other items.

Why is this, and how does this compare to previous periods?

Thanks to this cloud business intelligent, users are alerted to variances and can find answers by working with data before following up with other users.

So what is going wrong with our sales effort in Germany?

Clicking on this country from the graphic dynamically filters the dashboard by this region.


The updated dashboard reveals that margins in this country are just 11% compared to the company average of 15% which means German sales are dragging down overall margins.

Electronics discounts of 4% are widespread in this market but who is offering these reductions?

By clicking the item in Power BI we can see that 4 individuals are selling Electronics orders but only 1, Peter Brehm is responsible for these sales in the German market:


Clearly, this would prompt a sales manager to speak to Peter as a matter of urgency to understand the competitive pressures and other challenges they face when quoting for electronics orders in this region, and perhaps compare this performance to an earlier period.

These are just a few examples to demonstrate how you can drive improve sales performance by gaining crucial insights from the sales data you already hold in Microsoft Dynamics.

Evaluate Power BI by testing a demonstration dashboard and contact Preact and begin discovering insights from your own CRM data.