Advanced Functions PivotTable

So far, most of the analysis I have shown are relatively straightforward calculations. Now, I want to demonstrate the some of the more advanced capabilities of this platform.

Advanced Functions

Often, when we examine financial results, we want to compare it to a comparable timeframe from the previous year. Power Pivot has some built-in time intelligence functions.

Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date]))

YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())

For example, adding just two measures above to the Accounting Data table in Power Pivot enables me to produce the following PivotTable in a few clicks.

Time Intelligence PivotTable

Source: Ellen Su, Toptal Finance Expert Mismatched Granularities

As a financial analyst, one problem I often have to solve is that of mismatched granularities. In our example, the actual sales data is shown to the category level, but let’s prepare a budget that is only on a seasonal level. To further this mismatch, we will prepare a quarterly budget, even through the sales data is daily.

Mismatched Granularities – Budget Table

Source: Ellen Su, Toptal Finance Expert With Power Pivot for Excel, this inconsistency is easily solved. By creating two additional reference tables, or dimension tables in database nomenclature, we can now create the appropriate relationships to analyze our actual sales against the budgeted amounts.

Mismatched Granularities – Relationships

Source: Ellen Su, Toptal Finance Expert In Excel, the following PivotTable comes together quickly.

Mismatched Granularities – Budget vs. Actual Results

Source: Ellen Su, Toptal Finance Expert Further, we can define new measures that calculate the variance between actual sales and budgeted sales as below:

Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1

Using this measure, we can show the variance on a PivotTable.

Mismatched Granularities – Variance Results

Source: Ellen Su, Toptal Finance Expert Percent of Total

Finally, let’s examine sales in a particular category as a percent of all sales (e.g., category contribution to overall sales), and sales in a particular category as a percent of all sales of the same type (e.g., category contribution to seasonal-type sales). I created the two measures below:

Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'))

Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))

Those measures can now be deployed in a new PivotTable:

Percent of Total

Source: Ellen Su, Toptal Finance Expert Notice how the calculations are performed at both the category and seasonal type level. I love how quickly and effortlessly these calculations are performed on such a large dataset. These are just a few examples of the elegance and sheer computational power of Power Pivot.

Compression

Another benefit is that file sizes shrink. The original file size was 91MB, and now it is under 4MB. That is a compression of 96% of the original file.

File Sizes

Source: Ellen Su, Toptal Finance Expert How does this happen? Power Pivot uses the xVelocity engine to compress the data. In simple terms, the data is stored in columns rather than rows. This storage method allows the computer to compress duplicate values. In our example dataset, there are only four regions that are repeated over all two million rows. Power Pivot for Excel can more efficiently store this data. The result is that for data that have many repeating values, it costs much less to store this data.

One thing to note is that I used whole-dollar amounts in this sample dataset. If I had included two decimal points to reflect cents, the compression effect would lessen to a still-impressive 80% of the original file size.

SSAS Tabular

Power Pivot models can also be scalable to the entire enterprise. Let’s say you build a Power Pivot model that starts gaining many users in the organization, or the data grows to ten million rows, or both. At this point, you may not want thirty different users refreshing the model or making changes. The model can be seamlessly converted into SSAS Tabular. All the tables and relationships are retained, but now you can control the refresh frequency, assign roles (e.g., read-only, read and process) to various users, and deploy only a small Excel front-end which links into the Tabular model. The result is that your users could then access the deployed Tabular model with a small workbook, but not have access to the formulas and measures.

4) Data Visualization and Analysis

CUBE Formulas

One of the constant requests of my clients is that I create reporting that conforms to a strictly defined layout. I have clients that request specific column widths, RGB color codes, and pre-defined font names and sizes. Consider the following dashboard:

CUBE Formulas Embedded

Source: Ellen Su, Toptal Finance Expert How do we populate the sales numbers without generating PivotTables if all of our sales are housed with Power Pivot for Excel? Using CUBE formulas! We can write CUBE formulas within any Excel cell and it will perform the calculation using the Power Pivot model we have already constructed.

For example, the following formula is typed in the cell under “2016 Total Sales:”

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Calendar].[Year].[2016]")

The first part of the formula, highlighted in yellow, refers to the name of the Power Pivot model. In general, it is usually ThisWorkbookDataModel for newer versions of Power Pivot for Excel. The portion in green defines that we want to use the measure Total Sales. The part in blue instructs Excel to filter for only rows that have a Sales Date with a year equal to 2016.

Behind the scenes, Power Pivot has constructed an Online Analytical Processing (OLAP) cube with the data, calculated columns, and measures. This design allows the Excel user to then access the data by fetching directly with the CUBE functions. Using CUBE formulas, I have been able to construct full financial statements that conform to predefined layouts. This capability is one of the highlights of using Power Pivot for Excel for financial analysis.

Power BI

Another advantage of Power Pivot for Excel is that you can quickly take any Power Pivot workbook you build and quickly convert it into a Power BI model. By importing the Excel workbook directly into the Power BI Desktop app or Power BI Online, you can analyze, visualize, and share your data with anyone in your organization. Essentially, Power BI is Power Pivot, PowerQuery, and SharePoint all rolled into one. Below, I have created a dashboard by importing the previous Power Pivot for Excel workbook into the Power BI desktop application. Notice how interactive the interface is:

Power BI

Source: Ellen Su, Toptal Finance Expert One great thing about Power BI is the Natural Language Q&A. To demonstrate, I uploaded the Power BI model onto my online Power BI account. From the website, I can ask questions and Power BI constructs the appropriate analysis as I type:

Natural Language Q&A

Source: Ellen Su, Toptal Finance Expert This type of query ability enables the user to ask questions of the data model and interact with the data in an easier way than in Excel.

Another benefit of Power BI is that the developers at Microsoft are constantly releasing updates to it. New features, many user-requested, are pushed out monthly. Best of all, it is a seamless transition from Power Pivot for Excel. So, the time you invested learning the DAX formulas can be deployed in Power BI! For the analyst who needs to share his analysis to many users on varying devices, Power BI may be worth exploring.

Best Practices

Once you get started, there are a few best practices that you should follow.

The first is to thoughtfully decide what to import in the first place. Will you ever use the salesperson’s home address? Do I need to know my customer’s email address in the context of this workbook? If the goal is to aggregate the data into a dashboard, then some of the data that is available will not be necessary for those calculations. Spending time curating the data coming in will greatly alleviate issues and memory-usage later when your dataset expands.

Another best practice is to remember that Power Pivot is not Excel. In Excel, we are accustomed to creating calculations by constantly expanding our worksheets to the right. Power Pivot for Excel most efficiently processes the data if we limit this desire for manifest destiny. Instead of continuously creating calculated columns to the right of your data, learn to write measures in the bottom pane. This habit will ensure smaller file sizes and quicker computations.

Finally, I would suggest using plain-English names for measures. This one took me a long time to adopt. I spent the first few years making up names like SumExpPctTotal, but once other people began to use the same workbooks, I had a lot of explaining to do. Now, when I start a new workbook, I use measure names like Expense Line Item as Percent of Total Expenses. While the name is longer, it is much easier for someone else to use.

Real-World Use Cases

In this article I have presented only a handful of the ways in which Power Pivot for Excel allows you to take an important step beyond plain-vanilla Excel. I thought it would be useful to highlight some real-world use cases in which I’ve found Power Pivot for Excel is extremely useful.

Here are some:

* Analyze performance of a large portfolio of assets over varying time ranges: Since Power Pivot for Excel allows us to define measures that compare a time-period with a previous one, we can quickly have quarter-over-quarter, year-over-year, and month-over-month performance all on a rolling basis by writing only a few measures.

* Summarize accounting data using customized aggregation levels: By identifying each general-ledger line item by name, category, and financial statement, reports can quickly be created that include the appropriate line items.

* Chains can identify same-store sales: Using a table that maps when stores come online, financial results can be compared on a same-store basis.

* Pinpoint over- and under-performers in sales: PivotTables can be created that highlight the top five SKUs and bottom five SKUs by sales, gross margins, production timeframes, etc.

* Retailers can define calendar tables that use a configuration: Using a custom date table, a retailer can assign each day to a specific month easily, then daily sales results can be rolled into the corresponding month.

From Clunky Spreadsheets to Modern Workbooks

As financial analysts, we are required to perform complex calculations on ever-expanding datasets. Since Excel is already the default analytical tool, the Power Pivot learning curve is easy, and many of the functions mirror Excel’s native functions.

With the use of CUBE functions, Power Pivot for Excel seamlessly blends into your existing Excel workbooks. The computational efficiency gain cannot be overlooked. Assuming a 20% faster processing speed, which is conservative, the financial analyst that spends six hours a day within Excel can save 300 hours a year!

Additionally, we can now analyze datasets that are much larger than we could previously with our traditional Excel. With models designed efficiently, we can easily have 10x the amount of data that we previously were allowed in traditional Excel, while maintaining quick analytical agility. With the ability to convert the models from Power Pivot to SSAS Tabular, the amount of data that can be processed is 100–1,000 times what we can achieve in Excel.

Power Pivot for Excel’s ability to perform lightning-fast calculations on large amounts of data and still retain the ability to dive into the details can transform financial analysis from clunky spreadsheets to modern workbooks.

Article : Power Pivot For Excel

If you’re interested in trying Power Pivot for Excel out, below are some useful materials to get you started.

Useful References and Guides

> Collie, R., & Singh, A. (2016). Power Pivot and Power BI: The Excel user’s guide to DAX, Power Query, Power BI & Power Pivot in Excel . United States: Holy Macro! Books.

> Ferrari, A., & Russo, M. (2015). The definitive guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI. United States: Microsoft Press, USA.