How to Use Power BI and ‘R’ to Quickly Identify Business Insights

In statistics, the correlation coefficient or Pearson’s correlation is a measure of the strength and direction of association that exists between two continuous variables. The value of correlation, r, is always between +1 and –1. To interpret its value, see which of the following values your correlation r is closest to:

  • Exactly – A perfect downhill (negative) linear relationship
  • 70. A strong downhill (negative) linear relationship
  • 50. A moderate downhill (negative) relationship
  • 30. A weak downhill (negative) linear relationship
  • No linear relationship
  • +0.30. A weak uphill (positive) linear relationship
  • +0.50. A moderate uphill (positive) relationship
  • +0.70. A strong uphill (positive) linear relationship
  • Exactly +1. A perfect uphill (positive) linear relationship

Don’t make the mistake of thinking that a correlation of –1 is a bad thing, indicating no relationship. Just the opposite is true! A correlation of –1 means the data is lined up in a perfectly straight line, the strongest negative linear relationship you can get. The “–” (minus) sign just happens to indicate a negative relationship, a downhill line.  Most statisticians like to see correlations beyond at least +0.5 or –0.5 before drawing any conclusions. Here is additional information on Pearson’s correlation.

Putting it All to Use

This is all great knowledge, but how can we apply this in a business environment.  When I worked for a casino, we kept a daily record of Coin In (gross sales), High Temp, Low Temp, and Fuel price in an Excel spreadsheet and tried to find a correlation between these data points.  Try to find the correlation with this data.

Example Data Set

With Power BI and R, we can make this association all that much faster and with a slicer for interactivity to drill into the data points. I first downloaded the R Correlation Plot custom visual and then Excel spreadsheet from above imported into Power BI. I was then able easily identify the correlations in a matter of minutes.

Positive correlations are identified with increasingly dark blue circles; negative correlation is red. The greater, the bigger and darker the circle.  Selecting “Nov” from the calendar slicer, you will notice that the intersection between “Low Temp” has a positive correlation (blue circle), so the lower the temperature, the less you will realize in coin in for that month.

November Data

Selecting the “Jul” from the calendar slicer, and notice that the correlation is different. In this case, the correlation is zero between temperature and coin in.

July Data

Conclusion

I hope this helps you quickly identify business insights in your organization.  Please contact KTL Solutions to kick start your organization into becoming data driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM needs.


BARRY CROWELL, MBA | BI/EDW Solution Architect

Barry is a Microsoft SQL Certified Professional with a strong knowledge of the Microsoft’s BI Stack (SSIS, SSRS, SSAS and Power BI). He has architected, developed and deployed clients’ Business Intelligence needs using Microsoft’s BI Stack and/or Solver’s BI360.  His solutions have included SSIS ETL tools, SSRS reports and dashboards, Excel dashboards, Power BI reports and dashboards, and SSAS cubes. He has performed implementations as the lead consultant and/or end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry, and in various industries such as housing authorities, universities, Tribal governments, and casino hospitality. He possesses a Bachelor’s Degree in Accounting and Business Administration from Black Hills State University and a Master’s in Business Administration from La Salle University.  Barry’s experience in both the IT consultant field and experience as an accountant gives him the ability to understand the issues from both the IT and Finance prospective, and provide a solution that fits the needs for all parties involved.

Share this post

Related Posts