Power BI with your accounting data

Power BI Treemap showing Outstanding and Weighted Overdue Days by customer

Posted by Hugh Johnson on 12-Sep-2018 07:00:43
Hugh Johnson
Find me on:


In our Power BI Accounts Receivables Solution for Sage 50, we have used treemap to represent our customers’ balances and their weighted average overdue days in a simple, digestible manner.  

The area of each rectangle represents the amount owing and the colour indicates the weighted days overdue.  Bright red is bad (most overdue) and bright green is good (within due). Anything in between is well.

To aid presentation, I have segmented the customer base by balance.  The top 10 customers by balance each have their own rectangle and the rest are assigned to one rectangle called “Other”.  The reason that I have done this is to declutter a treemap that might otherwise have several hundred or more rectangles.

We measure the overdue amount on a weighted-average basis, so that a really small long-overdue balance for one invoice does not distort the overall picture for a customer.

What the treemap above shows you in an instant is that the top 10 customer balances account for around 60% of all receivables.  Of these balances more than half are overdue (on a weighted basis), and two customers with large balances are now significantly overdue.

You can see that the ‘Other’ branch of the treemap is neither bright red nor bright green.  That is because it is a mixture of weighted-average overdue.  You can drill down to see this. 


Why use a treemap and not a pie chart or donut chart?

Pie chart-1

Choosing your visualization is also an important part of creating a report. For instance, to show your balances by customer you could use a pie chart or donut chart.  The big difference in Power BI is that these two visualisations are just one-dimensional (in this case the outstanding balance by customer group. 

Donut chart-1

The treemap allows us to add a second dimension by colour, in this case weighted average overdue days.

Also if you are working on a rectangular canvas with limited space, you can pack more information into a given space with a treemap than a pie chart or donut chart.  Having said that, the circular shapes of the pie chart and donut chart can be a nice break from lots of rectangles.

Topics: Accounts Receivable