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: http://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 PowerPivotPro 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

Power BI Webinar

  Subscribe to PowerPivotPro!
X

Subscribe

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI. 

This Post Has 32 Comments

  1. Great post Avi! Looking forward to the webinar. I’m interested to see what a financial report looks like in Power BI.

  2. Hi Avi. When you say that you “publish” your data model to SSAS Tabular, I know you can restore a Power Pivot file in SSAS but how do you “publish” a file using Power Query? Do you re-create the PQ requests in SSIS or is there another way?

  3. Avi, very interested in the webinar, but the time-zones are a bit tough for us. Will it be recorded or available after the fact?

    1. Yes, it would be recorded, but you may need to register to be able to later watch the recording. So even if you’re unable to attend and intend to watch the recording, I would recommend that you still register. This is run/hosted by Microsoft, so I’m passing on the information that I have.

  4. Avi, I follow your academic insights and reap benefits from it. I’d love to hear you talk about your experience with finance dashboards.

    The link to register for the webinar goes to an error page (“Bad Request”). Can someone correct it, please? Thanks.

      1. I am having the same issue with the “bad request.” I’ll try from a different browser to see if that resolves it.

    1. I’m having the same issue. Were you able to register? Mine keep giving me the “Bad Request” screen when I click on Register.

      1. Greg, have you tried using a different browser? Others have indicated that Internet Explorer or Edge works better. For me it works in any browser so no idea what’s going on here. This one is actually hosted by Microsoft and they have setup the registration page and link. Hopefully you can make it work in a different browser.

    1. Uh…not sure what’s going on. The link works for me, no problem. Other folks have reported that the link works for them in IE but not in Chrome. So try a different browser.
      For me it works in Edge/IE/Chrome/Firefox 🙂

  5. EXCELLENT webinar “except” where we lost the audio – you were covering the excellent Excel model – can we get the Excel “model” as it says “Excel provides detailed analytical views (dummy data)”

    1. I agree, those were some great examples, but could you re-post the link regarding smart DAX filters or whatever you called it that enables the slicers to change the measures by Budget, Forecast, and Last Yr.

  6. Avi, thanks for your time today! The webinars are a great resource. At the end of the webinar you were talking about DAX vs. M and mentioned some users rely too heavily on M to do things like create a last year value. You said really that should be done with DAX using the time intelligence functions. Does this hold true when using a fiscal (ISO) calendar? This has been a major paint point for me. I use Marco Russo’s very helpful time intelligence complete patterns, but as far as I can tell, there are drawbacks including: (1) it requires calculated columns (in the calendar table) in powerpivot which SLOW down the model and (2) when calculating last year in DAX, there is not a way to add conditions (if statements).

  7. In agreement with Scott – excellent presentation. Unfortunately, I missed the first half of it, and am now trying to watch it after the fact (I had registered beforehand). Unfortunately, the audio is cutting out repeatedly. I believe it was stated that there would be a PDF of the slides – please provide the link once those are posted. Also, any idea on how to fix the audio issues so that I can follow-along w/ the PDF slides and audio at same time? Thanks!

  8. Is registering the only way to have access to the recording? I didn’t find out about it till today (really bummed). This would have been awesome to watch…

  9. This was very difficult to follow due to the technical issues and multiple controls. The sound seemed to be coming from one FLASH control and the video from another FLASH control. On the day of I couldn’t get sound for anything, so after about 30min I gave up. I tried Chrome and Edge. Chrome blocks flash for me and even right-clicking on the flash controls wouldn’t turn them on for the Event.On24.com site like it does in all other sites that have flash. I came back the next day and used Edge, which kept locking up periodically saying the On24 site wasn’t responding. I had to refresh multiple times and even then I’d lose audio or video and have to refresh. I have a Surface Pro 4 i5 with 16GB of memory, so it wasn’t my machine that couldn’t keep up.

    Maybe pass this along to the on24 site for feedback, or use a different provider for future webcasts. I think the technical issue for me really distracted from the content being provided. I’d love to see more, but would like a smooth experience.

  10. Technical issues aside (we’ve all been there), this was a great demo of the power of the data model, Excel as an ETL tool, and Power BI. I gave a presentation today to the execs, and the CAO even used your term “data drudgery” in the pitch. They were sold, and we are moving forward thanks to your demo. Keep up the good work!

  11. Avi,

    how did you manage to have a different symbol for the Formulas table in your example file (and how did you overcome alphabetical order) ?

  12. Great webinar! Very relevant! I am wondering, when consuming the data in both Power BI and Excel, where should the data model be created? Should it be created in Excel via PowerQuery and PowerPivot and then the excel workbook can be imported in Power BI with the whole data model? Or should it be created in Power BI and then ‘analyze in Excel’ can be used once it’s published to the web? Or another suggestion? In my specific case, the data source is represented by Sharepoint Lists which can be imported in both Excel and Power BI. The ‘issue’ that I have with the latter option (creating the data model in Power BI) is that when creating the Pivot Table I can only use measures for the values, and can’t directly use numerical columns.

Leave a Comment or Question