Member Since: 20th Jan 2012
I am a chartered accountant (England & Wales) who has worked in business intelligence for over 20 years. For much of that time I have designed and developed financial planning and analysis solutions using OLAP tools that have tight Excel integration. I have also authored training courses used to train the users of these solutions.
I have presented at various Excel and BI-related seminars and conferences, and am a Microsoft IT Certified Professional (BI).
I operate an Excel/BI information-and-products web site: http://www.excelcraft.com
5th Apr 2019
A good overview, Hugh. But I think you skip over the fact that the data engine in Power BI is the same as Power Pivot in Excel. So someone wanting to learn Power BI could get their feet wet with Excel and Power Pivot. Having built the data model and become comfortable with it, they can upload it to Power BI to take advantage of its sophisticated visualizations user-friendly interface.
1st Jan 2019
An insightful and humorous digest. Thank you.
13th Dec 2013
DAX time intelligence functions
A very nice presentation of a subject that can be confusing. Do these functions assume a standard calendar year, Jan 1 to Dec 31?
14th Sep 2013
It's also worth noting that Excel 2013 models built with the Power Pivot add-in, that use DAX functions, work fine in versions of Excel 2013 that support the Data Model, but without the add-in. I believe there is a 10 MB limit on the workbook size though. So potentially a user equipped with Power Pivot can create models for use by other users not so equipped.
14th Sep 2013
Understood. I was wondering if you have found a way to add Calculated Fields to the Data Model (sans Power Pivot) as I haven't. Also Calculated Items (PivotTable Analyze tab) that can be created on a regular PivotTable, is disabled on a PivotTable built off of the Data model. Not a problem if you have Power Pivot, but limiting if you don't.
13th Sep 2013
Are you running the Power Pivot add-in and entering your CALCULATE function in the measures grid of the Power Pivot window?
20th May 2013
PowerPivot vs Data Model
This series of tutorials has been an excellent introduction to the more powerful and robust data analysis options in Excel 2010/2013. I liked you rather charitable comment regarding the lack of PowerPivot in Excel 2013 (except in Office 2013 Professional Plus): "Perhaps in order to compensate for this, Excel 2013 does include as standard the data model that is a key element of PowerPivot."
17th Dec 2012
"So why are so many accountants still fiddling around in Excel?"
That is indeed an excellent question and one frequently discussed both on this site and elsewhere.
George, thank you for your excellent recount of the value of the relational databases as a platform for accounting and financial reporting systems. There are more facts and Dates (pun intended) than I care to admit remembering.
Without getting embroiled in the "fiddling around in Excel question" I would just put forward this view. If you are going to use Excel as an analysis and presentation medium then PowerPivot offers a repository and environment where tables can be related and where "calculated columns", using DAX formulae, are more robust than regular worksheet formulae. Of course errors can still be introduced and this is no single version of the truth. So, as always testing by you and by someone else is essential.
I think that the fiddling around in Excel has a lot to do with the desire to be independent of or less reliant on IT support and not doubt at times has shown itself to be a double-edged sword.
19th Jun 2012
PowerPivot and Excel Cube Functions
Simon,thank you for the reminder on SUMPRODUCT(). That's one I overlook too often.
Worth a mention here is the support in PowerPivot/Excel 2010 for Excel cube functions that blows away GETPIVOTDATA(). There is no requirement to have a visible PivotTable and the functions can be created by converting a PivotTable. If your data is already in an Excel range you can get it into PowerPivot using the Create Linked Table button on the PowerPivot tab. Then build a PivotTable off of that and click the Convert to Formulas on the OLAP Tools tab (disabled in the case of a regular Excel PivotTable) and lay out your report the way you want.
You can link to a chart of accounts table which contains a column for report groupings/hierarchy of NL codes.
Here is a video comparing PivotTables from PowerPivot with those from a regular Excel range.
20th Jan 2012
A close relative of PivotTables
An Excel feature related to PivotTables that is under-appreciated (in my opinion), is Cube Formula Reports.
In Excel 2007 these are only available to users who access OLAP cube data, usually from Microsoft Analysis Services. But with Excel 2010 and the PowerPivot add-in you can build them from a PowerPivot database that can be an Excel table or range, an Access DB or a variety of other data sources.
Having created a PivotTable from this data, there is an option (under OLAP Tools on the PivotTable Tools tab) to Convert to Formulas. Then you have Excel's cube functions that are still dynamically linked to the data but can be moved wherever you want (like regular Excel functions) and don't have the layout limitations of PivotTables. I find these very powerful for financial and management reports that require careful placement.