Power BI with your accounting data

Power BI scatterchart showing overdue invoices by customer

Posted by Narayani Ambar on 14-Sep-2018 15:50:19

 

Power BI Scatter Chart - Overdue Invoices by Customer

In the second page of our Accounts Receivable Solution for Sage 50 we have a page aimed specifically to help the collections team focus on activities that will have the greatest impact on performance. 

On this page, we use a scatter chart to give an overview of all customers with overdue invoices.  Scatter charts are great for vieing many variables together.  Unike Power BI pie charts and donut charts that can only plot one variable (e.g. customer balance) against an entity (e.g. customer), or a treemap that can show two variables (e.g. balance and weighted days overdue), you can plot four variables on a scatter chart.

In the chart above, we have the following variables plotted for each customer:

  • Outstanding overdue amount (Y-axis)
  • Average overdue days for all overdue invoices (X-axis)
  • Weighted average overdue days (colour)
  • Total sales in the last 52 weeks (size)

Added to the chart I have added two median dotted lines for the x and y values to distribute the customers into four dynamic segments (so in a way adding two more variables).  Scatter charts allow you to add these via the analytics tab in the configuration of the visual and you don't have to worry about calculating these median lines.

From an accounts receivable point of view, the segment that is of most concern to us is the one in the top right-hand corner.  That is, the segment containing customers with the highest amounts outstanding and the most overdue.

What a scatter chart is really useful for is to help you spot outliers.  In top right segment we can see one really big outlier, that is Advanced Care Rx Pharmacy.  It is our largest overdue debtor (y-axis) and also one of our largest customers over the last 52 weeks (size of the circle).  As it is coloured in red, it also means that it has a high weighted average overdue days value (weighted average across all overdue invoices for that customer).

Three other customers stand out in particular as having large overdue balances but not as old as Advanced Care Rx Pharmacy.  These are in the top left segment.

We can see that the vast majority of overdue customers have balances of less than £20k and are overdue between 10 and 90 days.

What a scatter chart (also known as a bubble chart) is not so good at is giving you the detail behind anything that is not an outlier.  But that's ok, because on the same page of your report you can have other interactive charts to give you this detail.

You can try this out on page 2 of our interactive Power BI example report for accounts receivable.

Topics: Accounts Receivable

Power BI with your accounting data

Time-saving tips, tricks, templates and tutorials

Subscribe to this blog for practical shortcuts to driving real insights from your accounting data with Power BI.

Expect:

  • How-to videos
  • Example reports
  • Sample formulae
  • Data modeling tips
  • Free Power BI templates
  • Tips for optimising Sage 50cloud

Subscribe to Email Updates

Recent Posts