Using Power BI to build Financial Dashboards

UPDATE: Thanks for attending the Webinar.

• Webinar Recording:Click to watch the recording on our YouTube Channel
• Power BI File Download: I am unable to share the exact file I demo’ed but I can share a close copy which uses some of the same techniques.
  PBIX Download: https://ppvt.pro/example0816
  Webinar where I walk through that PBIX Model: Webinar Mar 2016

Tue Aug 16th I would be presenting a Power BI Webinar, detailing my experiences and learning from using Power BI to build Financial Dashboards, for one of our key clients. This webinar is geared towards Excel users moving to Power BI.
Click here to register.

Keep reading for a brief tour of this project.

Goal

Our goal was that financial analysts, rather than wrestling with data and queries, could instead, focus on serving their customer – the business. By providing smart analysis, insights and financial guidance.

To do so we needed:

• Dashboards/Reports that were effective, easy to use and visually appealing
• Served by a BI Solution that was:
  • Agile: quick to build with limited resources
  • Scalable: scale gracefully over time as we added datasets and complexity in our model.

In essence, we wanted our BI solution to not just be a pretty picture but also have “strong bones“.

Solution

Our final solution involved: Excel with Power Query and Power Pivot to author the data model, published to SSAS Tabular, with reporting in Power BI and Excel. See image below (click to enlarge):

Operations Finance: BI Architecture (click to enlarge)

The BI solution supports a rich set of dashboards and reports in both Power BI and Excel. Here’s how we used each tool:

Power BI

• Rich interactive, mobile-ready dashboard and reports
• Personalized Dashboard using Row Level Security at SSAS Tabular. This shows each user a customized dashboard based on the Organization Group they belong to.
• Natural Language Q&A via Power BI or directly from desktop using Cortana

Excel

• Detailed Analytical View, where analysts can deep-dive, slice and dice data any way they want using the flexibility of a familiar tool – Excel

Power BI Dashboard and Reports (dummy data)

Power BI also enables Q&A and Cortana

Excel provides detailed analytical views (dummy data)

Even though Excel has been the tool of choice for Finance for a long time, we discovered that Excel and Power BI are indeed better together. Each tool can be used for specific usage scenarios and they complement each other.

Conclusion

At P3 Adaptive we often equate the Dashboard/Reports (Visualization Layer) to the light-bulb, and the underlying Data Model to the power-grid (read article).

Visualizations are important, but the power lies with the Data Model

The light-bulb is extremely important – the reports/dashboards are the ones that light our path ahead by providing us insights.

However the Data Model involves the bulk of the work and provides most of the inherent value when building a BI solution. The good news is, all that power lies within easy reach. You can easily build BI Data Model by leveraging either Power BI Desktop or Excel with Power Query and Power Pivot.

Here is what the Data Model let us achieve:

• Combine Multiple Data Sources with Easy Refresh: Data Model lets you combine multiple official sources (e.g. P&L and People perspective) with your own custom datasets/mappings. All, while providing one-click or automated refresh.
• Single Source of Truth: All reports (in Power BI or Excel) would connect to this single Data Model as the verified source of the truth. This avoids any confusion around numbers not matching across various reports.
• Analyze Your Data Easily: You can easily slice-and-dice your data using any Dimension or Lookup Table in the Data Model. E.g. by Channel hierarchy or Account hierarchy.
• Define Complex Calculations: Power BI offers a powerful formula language in DAX, which allows us to build sophisticated functionality in our reporting. Such as ability to easily select a time frame – Closed Month, Quarter-to-Date, Year-to-Date; select a specific comparison – Actuals against Budget/Forecast/Prior Year Actuals.

Leveraging DAX measures in the Data Model allows users to make quick and easy selections in the Power BI report (dummy data)

Excel and Power BI can help you build a BI solution, which has beautiful visual and interactive reports, but is also agile, scalable and has strong bones!

Register for Power BI Webinar

For a more detailed presentation on this topic, click to register for the upcoming Power BI webinar.
Date: Tue Aug 16th