Year on Year Sales Review

Report Profile

This Power BI report analyses sales in the current year compared to sales in the prior year.

This report would replace a previous weekly sales analysis dashboard produced in Excel and not only produces a far more interactive report that reviewers enjoy analysing, but it also takes much less time to produce, meaning that the analysis can instead become daily.

Excel Report

  • Sales staff download a weekly sales Excel report of approximately 15,000 to 25,000 rows from the Sales system and combine it with other data to enrich the Sales dataset
  • While Sales data can be downloaded daily, the dataset is dirty and takes hours to clean, transform and get added to previous datasets to get a year to date view . It is therefore not feasible to analyse the data more frequently.
  • By the end of the year, the combined datasets can be more than the Excel worksheet row limit, meaning that the Sales team needs to split data between different tabs.
  • Because Sales Data is split over different tabs, the Sales team don’t use PivotTables and instead use aggregation functions like SUMIFS and COUNTIFS. Analysis is therefore static.
  • Due to holding multiple worksheets of hundreds of thousands of rows, the report is very slow
  • Because of the slowness of the supporting files, Sales staff cannot answer ad-hoc queries quickly.
  • This also means that senior staff can’t interrogate data themselves

Power BI Report

  • The Sales team connects Power BI to folders of Excel files and uses Power BI’s ability automation capabilities to transform the dirty Sales datasets and then add the Sales files together.
  • The Sales team can use Power BI to automatically merge the Sales data with other datasets to enrich the analysis
  • The Sales team are able to simply add new Sales dataset files to the connected folders and allow Power BI to perform the transformation.
  • Sales staff can now download daily sales Excel reports of approximately 2,000 to 4,000 rows from the Sales system. Analysis can now be performed daily.
  • Power BI uses DAX to perform dynamic analysis rather than static analysis
  • Power BI is built to deal with millions of rows so it is quick to respond
  • Sales staff can build ad-hoc analysis to respond to senior staff questions as queries are asked
  • With Power BI Pro, Senior Staff can interrogate the analysis themselves.

Excel Disadvantages

  • Sales reports take around a day to prepare
  • Sales reports can only be prepared weekly
  • Senior leaders are not able to respond to trends or changes quickly resulting in lost sales
  • Sales analysts spend much more time collating data than analysing trends
  • Ad-hoc analysis is slow

Power BI Advantages

  • Analysis is performed daily
  • Sales report is ready in 30 minutes
  • Data collation is quick and much more time can be spent on analysis
  • Senior leaders can respond to sales trends quickly to increase sales
  • Ad-hoc analysis can be built quickly