Sage Power BI Dashboard for Financial Analysis

This Sage Power BI Dashboard was created for an FMCG company selling around the UK. They had historical data in Sage 50 and then migrated to Sage 200 which is what they currently use. One key challenge was to combine the historic data with the data from the new version of Sage.

They needed automated Sage Power BI dashboards to monitor the sales, costs, profit margins, credits and other financial metrics. The client needed to break down these metrics were by product, customer, supplier and product category. 

Sage and Power BI Integration

We extracted the historical data from Sage 50 into an Excel spreadsheet and brought into the model as a static file. The historical data would never change so there was no need to code an integration.

As for Sage 200, there is an underlying SQL database for every Sage 200 instance. Vidi Corp connected Power BI to this underlying Sage 200 database and automated the data refresh this way. The data from the 2 systems was brought to a common format and combined inside of Power BI.

The numbers inside of the Power BI dashboard were matched to Sage 200 to ensure data accuracy. This ensured minimum discrepancies and elevated the customer trust towards the Power BI dashboard. 

Sage 200 Database Structure

The data format inside the Sage database is not intuitive to use and presents a significant challenge in projects like this. The database contains 1000+ tables which are documented in this document. One key table that you would likely need to use is SOPOrderLineProfitAnalysis as it contains the data on revenue and costs. Below are some useful calculations:

Revenue = SOPOrderLineProfitAnalysis[IssueRate]*SOPOrderLineProfitAnalysis[LineQuantity]
Cost of goods sold = SUMX(SOPOrderLineProfitAnalysis,
    IF(MIN(Costs_Type[Type])=”Last Buying Price”,
SOPOrderLineProfitAnalysis[LineQuantity]*SOPOrderLineProfitAnalysis[c_Costs_Rate_Last
BuyingPrice],
SOPOrderLineProfitAnalysis[LineQuantity]*SOPOrderLineProfitAnalysis[c_Costs_Rate_List
Price]))

We really encourage to work with a professional Power BI consultancy with some knowledge of Sage database schema. This would significantly speed up your progress as learning the Sage 200 database from scratch could be difficult.

Sage Power BI Dashboard

The key metrics in this Sage Power BI dashboard are number of orders, sales, average order value and gross profit. Those metrics were compared to the previous period to give them more context.

Sales by product supplier were also analysed to help manage supplier relationships. Top 10 and bottom 10 products were also analysed to drive decisions about which products should be discontinued or marketed more. 

The geographical split of customers was analysed to measure local market penetration. 

The filters below allowed the user to select the exact metrics to display in the bottom tables. The users also could select whether to break down the metrics by supplier, product group or product. 

Do you also have a project where you need to analyse financial data or data from Sage? If so, contact us now to discuss the nuances of this kind of projects!