During my last blog post, I walked you through creating a financial dashboard using Power BI with Dynamics GP data. I have created additional measures now so we can compare the same period last year, calculate the variance, and the percent of change. The financial dashboard now looks like this.
If you look closely at the bottom left, you will see two additional reports for Sales Summary and Customer Analysis.
Sales Summary
The data used from Dynamics GP for this report are the Sales Order Processing (SOP) tables, Inventory, (IV) and Customer (RM) tables. The Sales Summary contains the same report filters, Year, Month, and Company as the Financial Summary report. The top left visual shows the total sales by product class ranked in descending order. The bottom left visual shows the total sales by customer class/sales channel. The top two visuals in the middle and right show the total sales and transactions based on the filters selected with a seven-day moving average behind them. The bottom right visual shows a comparative between cumulative sales and costs.
Customer Analysis
The Customer Analysis Report allows you to dig into Dynamics GP sales data by Customer, Year, Month, and Company. By selecting a customer on the left, you get to see three years of comparative sales, sales detail, last sales date, and amount, total sales, sales last year, total profits, and percent sales growth for that customer.
Future Dashboard developments
In the coming months, I will be planning additional Power BI reports associated with my Dynamics GP data. These reports will include:
- Vendor Analysis
- Inventory Analysis
- Field Service – Contract and Service Call Analysis
Stay tuned for the future developments of the reports and an overview of their functionality. For help with building reports like this, please contact KTL Solutions to kick start your organization into becoming data driven 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.
