Forum Discussion
How to set up scorecard with full Income Statement and Performance Metics
I've been challenged by our organization to move our performance scorecards from excel into Power BI so they can be more dynamic and available sooner to the business leaders. The scorecard includes a 17 line income statement and 22 Performance Metics (see screenshot below). All of the data comes from an Oracle Essbase Retrieve through excel and provides MTD, QTD and YTD amounts, along with variances to Prior Month, Prior Quarter Year and Prior YTD. Many of the performance metrics require calculations to data after it has been pulled from Essbase. I have built the retrieve that has all the data points (measures) required, and made it dynamic to an input tab so it can be updated easily from month to month. So as you can see, I have quite the challenge ahead of me, and I'm having a hard time deciding where to start. I don't even know if this is possible or worth the effort. I'm looking for a some guidance on what type of visuals I should use. How do I pull in the variances. Should I perform the data calculations in Excel before I pull the data into PBI or create measures in PBI to do that. The list of questions grows by the second, but those are the key ones I have right now. Thank you in advance!
Thank you both for your input! We found a work around by uploading the excel file directly into the online workspace, then pinning the individual divisional scorecard tabs in the workbook to their respective performance dashboards. We will still work to put pieces of the metrics into visuals, so both of your recommendations will help us work through that. Thank you again!
4 Replies
- ImkeF
Community Champion
Whatever is behind the assumption that PowerBI would deliver the data sooner to the business leaders, must have something to do with the way that the new Power tools handle the import-process and the modelling capabilies. Which are extremely powerful, but are also included in modern Excel as well: Power Query (or Get & Transform in 2016) and Power Pivot are included in Excel from 2010 onwards (some of them as add-ons). What you're missing in Excel is the powerful visualisation-capabilities. There you can cross-filter from one viz to another. But if your requirement is just a table, then Excel will probably be the better choice.
I've published a blog-series about P&L-solutions like you requested here: http://www.thebiccountant.com/2017/04/24/kpis-in-easy-profit-and-loss-for-powerbi/
But it you're new to the tools, you'll probably have to learn a lot in order to implement that on your own unfortunately.
Thank you both for your input! We found a work around by uploading the excel file directly into the online workspace, then pinning the individual divisional scorecard tabs in the workbook to their respective performance dashboards. We will still work to put pieces of the metrics into visuals, so both of your recommendations will help us work through that. Thank you again!
v-yulgu-msftMicrosoft Employee
Hi Anonymous,
Thanks for your sharing. Would you please mark the helpful reply or your shared solution as answer so that it can be a helpful guidance for those having similar concern?
Thanks,
Yuliana Gu
- v-yulgu-msft
Hi Anonymous,
Should I perform the data calculations in Excel before I pull the data into PBI or create measures in PBI to do that.
If you created reports in Power Pivot in excel file, you can directly import Power Pivot data model into Power BI desktop. Power BI supports all data model properties such as relationships, measures, hierarchies, and KPIs. You can refer to this video to learn how to import power pivot.
If you only load data into Power BI from data source, you need to re-create table relationships and measures using DAX. Here are some links for your reference:
Calculating Month-To-Date and Week-to-Date
Year to Date in Previous/Prior Year
Regards,
Yuliana Gu
