Use Power BI and R to Quickly Identify Business Insights

In statistics, the correlation coefficient or Pearson’s correlation is a measure 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 1. A perfect downhill (negative) linear relationship
  • 0.70. A strong downhill (negative) linear relationship
  • 0.50. A moderate downhill (negative) relationship
  • 0.30. A weak downhill (negative) linear relationship
  • 0. 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 are lined up in a perfect 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. Additional information on Pearson’s correlation can be found here.

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 find the correlation with this data.

Excel Casino data

With Power BI and R, we can make this association all that much faster and with slicer for interactivity to drill into the data points. I first downloaded the R Correlation Plot custom visual located here 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 the that month.

Power BI and R Correlation Plot

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.

Power BI and R Correlation Plot image 2


Hope this helps you quickly identify business insights in your organization.

WOW! A Dynamics GP multi-company Financial Dashboard – Part Deux

Back in October of 2015, I posted a blog about how to modify Jared Hall’s awesome Excel Financial Dashboard that you can download here. The problem with his solutions is that most Dynamics GP environments are multi-company setups making these dashboards not very functional without navigating and opening multiple Excel spreadsheets to view each company’s dashboard. So, I took it upon myself to modify the one provided by Jared Hall to work in a multi-company Dynamics GP setup, but that was so 2015!

This is 2017, so let’s create our Financial Dashboard in Power BI. The best part about using Power BI over Excel is that we can make is available through the Power BI service and via the iOS and Android Apps. Making it a much better option for mobility and availability.

The Parts to the Solution

For my solution, I choose to use the following tools:

  • A Small DataMart
  • Power BI Designer
  • Power BI Service

The DataMart

I chose to implement a DataMart to simplify the importing of the tables into the Power BI Designer. I created the following tables to implement my Financial Dashboard:

  • DimAccount
  • DimHeader – to summarize the report layout
  • DimDate
  • DimCompany
  • FactFinance – General Ledger data from the GL20000 and GL30000 tables of each company

Dashboard Design

I imported the above tables into Power BI Designer rather than using the DirectQuery mode. Using the Import option will allow for the full functionality of DAX and more importantly the Time Intelligence functions of DAX. Your table relationships should look like the screen shot below after you import the data.


The DAX Measures

The Profit & Loss Statement layout is handled by several DAX measures, the DimHeader table and the Sign and Report Sign columns within the Account table. I created the following DAX measures in the order listed below:

  1. Header Order
  2. HeaderCalcType
  3. Dollars
  4. Dollars with Sign
  5. Dollars with Report Sign
  6. Running Dollars with Sign
  7. Current Period
  8. Cumulative Sales (Selected)

The code for the DAX measures can be downloaded here from the Finance Dashboard template.

Visualize This 

An easy to use modern multi-company cloud-based or mobile app Finance Dashboard that shows your critical data, so you can spot trends, share insights, and make smart decisions.



I’m loving the Power BI Service and all the modern cloud-based analytics that it can bring to your dashboards and reports.

Dynamic Security – a look at the final product


Recently I posted a blog titled “How to implement Analysis Services Dynamic Security based on Microsoft CRM user access rights”. That post talked about how to implement the back-end structure needed to put it in place but didn’t show the result of all that hard work.

The dynamic row level security (RLS) that we setup inside Analysis Services will work within reports and dashboards built inside Reporting Services, Power BI and Excel. Power BI has other ways of deploying RLS when not using an Analysis Services connection. You can learn more about that here:



The End Result

We built are Dashboard in Power BI Designer and published the Dashboard to our end users. One of the end users “Tom” has access to 558 CRM Customer account within the US. When he accesses the Dashboard, his can view results that he has privileges to. (See Figure 1 below)

Power BI dashboard image 1

Figure 1 – Tom’s access view

If “Tom” tries to access an area that he doesn’t have privileges to, say Canada, the dashboard allows him to select the filter and changes the chart(s) results to show no data. (See Figure 2 below)

Power BI dashboard image 2

Figure 2 – Tom’s view with Canada selected

Additionally, the chart(s) change based on the end user that’s accessing the dashboard. In Figure 3 below, I show an administrative user accessing the dashboard and the result shown to them when selecting the “Canada” filter.

Power BI Dashboard image 3

Figure 3 – Administrative user view of Canadian Sales


I hope this helps you with your future Analysis Services development and Dashboard projects.

WOW! A Dynamics GP multi-company Financial Dashboard

Dynamics GP can deploy some great Excel dashboard’s in GP2015 and Jared Hall created a awesome Financial Dashboard that you can download here. The problem with all of these solutions is that most Dynamics GP environments are multi-company setups making these dashboards not very functional without navigating and opening multiple Excel spreadsheets to view each company’s dashboard. So I took it upon myself to modify the one provided by Jared Hall to work in a multi-company Dynamics GP setup.

Changing the Data Connections

The first changes made where to the Dashboard tab to add a form control and add a new tab with a data connection to the Dynamics SY01500 table for the list of active GP databases and then assign that list to the form control (figure 1).


I then added stored procedures in the Dynamics database with three input parameters. Two from the original parameters with Jared Hall’s Financial Dashboard of @UserDate and @TimeUnit and additional parameter of @Database. The @Database called the original stored procedure in the company’s database provided by Microsoft and passed the @UserDate and @TimeUnit into it to produce the intended results. This solved the KPI queries within the original Financial Dashboard. There were several OLE DB Queries in the Financial Dashboard for Budget, Cash Balances, Current Financial Data and Prior Financial Data. I used the original code from the queries and created individual stored procedures in the Dynamics database with one parameter of @Database. Once the stored procedures had been created, I changed the connections in the Current Financial, Prior Financial, Budget and Cash Flows tabs to user the Database connection calling the stored procedure with the @Database parameter mapped to the form control’s output (figure 2).


Refreshing the Pivot Table Cache

That left me only one obstacle that I discovered after all of this hard work. My Key Performance Indicators section was updating when I selected a new company from the drop down list. What I discovered was while the Financial data was updating in the Current Financial, Prior Financial, Cash Flows and Budget tabs the pivot tables Caches where not updating. I solved this by adding a little more VBA to the Financial Dashboard to automatically update all pivot tables caches when data source data changed. To do this created a VBA module and added the following code:

            Sub RefreshAllPivots()

                        Dim PC As PivotCache

                        For Each PC In ActiveWorkbook.PivotCaches


                        Next PC

End Sub

I then called the sub from the Financial data sheets whenever the worksheet changed.

Fabulous! A Multi-company Financial Dashboard

What we end up with is a multi-company Financial Dashboard with no need to navigate to different spreadsheet to get a view of each company’s current financial outlook.


Hopefully this helps you start your journey down Excel Dashboards in Dynamics GP. 

Visualizing your Financial Statement with a Waterfall Chart

In July, Microsoft introduced six new chart types that will be available as part of Office 2016. Today, I’m will show you how to take advantage of the waterfall chart, one of the most popular visualization tools used in small and large businesses by modifying Dynamics GP’s Excel Financial Dashboard with Excel 2016 to add a Waterfall chart.  A waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values and is very useful in analyzing an income statement.

Dynamics GP’s Standard Financial Dashboard

The first thing you need to do if you haven’t already is deploy Dynamics GP’s Excel Report to either a network drive or SharePoint site. Once deployed, your financial dashboard should look like this.


We are going to modify it to add additional detail to the Revenue and operating Expenses. To do that we insert one row below the Net Sales and three rows below the Operating Expenses. We they add the “Discounts & Returns” header to the newly inserted row below Net Sales and rename the “Net Sales” to “Gross Sales”. We then rename the “Operating Expenses” to “Wages & Benefits” and add “Expenses”, “Operating Income” and “Depreciation” to the remaining inserted rows. The final product should look like this:


The next step is to replicate the “Revenue” tab in the spreadsheet and change the title to “Discounts” and then modify the filters in each tab to only look at gross revenue and discounts & returns. Moving on to the Operating Expenses, I replicate the “OpExp” tab three times and rename those tabs – Expenses, Depreciation and Wages. I change each tab’s filter to only look at the appropriate expenses for each category. I then link each of the tabs results back to the Dashboard tab which produces the final results:


Waterfall Charts – making financial statement analysis easy

Waterfall charts work off of positives and negatives so I first have to convert my positive expenses into negative number. To do that I copy the Indicator section of the Dashboard and paste it into Column M. From there I change the Expenses into negative and change the calculations for the sub-totals of Gross Profit, Operating Income and Net Income.


Its now time to insert my waterfall chart. To do that I highlight the Gross Sales through Net Income rows and columns as shown on the below screen shot.


I then navigate to the Excel 2016 Insert tab and click on the Waterfall chart icon. This will add the waterfall chart to my spreadsheet.



Making the Waterfall chart understandable

To make our Waterfall chart understandable, we want to set visual checkpoints to the subtotals. In any Waterfall chart, you might set a subtotal to show the absolute value rather than as a floating value relative to the previous column. In this example, the line item accounts—Net Sales, Gross Profit, Operating Income and Net Income—are all totals that behave like checkpoints in understanding our financial statement. Currently the chart characterizes these accounts as increasing cash inflow, resulting in a positive skew.

To set a subtotal, select the data point and then right click the data point and select Set as Total from the list of menu options.


Once you have set all of the check points, your modified Financial Dashboard should now look like this:



Excel 2016 has a lot of new and exciting feature that are really enhancing it as a business intelligence tool.  I look forward to more great features in the coming months.