Power BI R Matrix Bubble Chart Sales Opportunity Analysis

A powerful Power BI R graph, customized matrix bubble chart, to visualize 3 facts by 2 dimensions at the same time

What

Sales, % of opportunities won (hitrate), and margin % are some of the basic parameters of sales analytics. Typically, these are compared across different dimensions like products or sales representatives. Here I present a powerful Power BI R visual, matrix bubble chart, to analyze sales, % of opportunities won, and margin % with two selected dimensions.

How

One needs to have R installed on the computer or to the server / enabled in the cloud environment. As a standard, Power BI does not have available a matrix bubble chart. In the marketplace potential custom visualizations are not enough adjustable. One can give to the R graph any measures or columns from the Power BI model. Filtering works too.

Here is a guide to get started: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-r-visuals
Here is a link to some details: https://docs.microsoft.com/en-us/power-bi/visuals/service-r-visuals

If you are making visualization for reporting and follow-up and not just as a part of a case study supporting for example machine learning, it might be better to present something else than bubble charts with multiple dimensions. See my post: Three Key Practices to Perfect Data Visualization.

Analysis example

• The edge color of a bubble tells about the margin % of a product.
• The fill color of a bubble tells about the % of opportunities won (hitrate) of a product.
• The bubble size illustrates the number of the opportunities.
• In the X-axis is the product dimension.
• In the Y-axis is the free feature bundle given together with the product dimension.

This could be an example from the car industry. The question could be what bundles we want to offer with what products and how should we price the product & bundle combinations.

Example observations from the graph

1. Starting from the largest bubble: Model X & Bundle A has a high volume, a good hitrate and margin. Why Model Y & Bundle A has a bad hitrate and volume? This should be studied further.
2. Low margin & high hitrate: Model Y & Bundle B has quite a low margin and an excellent hitrate. It might make sense to increase the sales price. On the other hand, Model X & Bundle B has an average margin but a low hitrate and volume. We need to be careful as Bundle B delivers significant volume for Model Y.
3. High margin & ok hitrate: Model Y & Bundle C has a good margin and an average hitrate. What is the profile of the bundle, could lowering the price increase the sales remarkably? Model X & Bundle C has a good volume.
4. Margin % is not the same as the monetary value of margin. A more expensive product can have the same margin % than a more affordable product but the more expensive product would still produce more margin for a company. The graph presented here works best for products and bundles with similar price points and market environment. Supporting other graphs should be used.

Code

# Paste or type your script code here:

#change lib path to were you have ggplot2 and scales installed 
library(ggplot2, lib="D:/Program Files/R/R-3.6.3/library") 
require(ggplot2)
library(scales, lib="D:/Program Files/R/R-3.6.3/library") 

#change columns Product, Free_feature_bundle, dataset$... to your columns in the Power BI model selected to the R visual
ggplot(dataset, aes(x = Product, y = Free_feature_bundle, label = paste(round(dataset$Opportunity_count, digits=0), round(dataset$Hitrate_perc, digits=2)*100, round(dataset$Margin_perc, digits=2)*100, sep=";"))) +
    geom_point(shape = 21, aes(size = round(dataset$Opportunity_count, digits=0) , fill = round(dataset$Hitrate_perc, digits=2)*100, color = round(dataset$Margin_perc, digits=2)*100, stroke = 3)) + 
    geom_text(hjust = 1, size = 4) +
    scale_size(range = c(15,50)) + #scaling of the bubbles
theme_bw()+ scale_fill_gradient2("Hitrate % (fill)", low=muted("red"), high=muted("green"),  midpoint =  
 mean(dataset$Hitrate_perc*100)) +theme_bw()+ scale_color_gradient2("Margin % (edges)", low=muted("red"),  
 high=muted("green"),  midpoint = mean(dataset$Margin_perc*100)) + labs(size = "Number of  opportunities (size)")

Sample Power BI file

Here is a link to the example Power BI file: https://drive.google.com/file/d/19ABGkwgOSGTzo9UTcVHgpoN1WIn54oe_/view?usp=sharing