Excel 2013 Pivot Table Data Crunching (MrExcel Library) by Bill Jelen & Michael Alexander

Author:Bill Jelen & Michael Alexander [Jelen, Bill]
Language: eng
Format: epub
Publisher: Pearson Education
Published: 2013-01-07T07:00:00+00:00

Other Benefits of the PowerPivot Data Model in All Editions of Excel

You get a number of other side-effect benefits of running your data through the Data Model:

• Count Distinct becomes a calculation option. This was previously hard to do. Excel tricksters would add a column to the original data that divided the COUNTIF of a field into 1. If a customer showed up five times, the calculation would evaluate to (1/5) or 0.2. They would then add up the five records with 0.2 and to get one unique customer. If you’ve ever gone through this painful process, you will be thrilled to know Count Distinct is two clicks away. Ditto for those of you who wanted a distinct count but could never get it to work.

• You can include filtered items in grand totals. Create a pivot table showing the top ten customers. The grand total has always been just the ten customers you see. Now, you can make that total include all of the small customers who were filtered out of the report. This feature has always been in the Subtotals drop-down on the left side of the Design tab in the ribbon, but it was perpetually grayed out. Run your data through the model, and it becomes available.

• Named sets were introduced in Excel 2010, but only for people with OLAP data. Named sets let you create pivot tables with last year’s actuals and next year’s budget. By taking your data through the PowerPivot engine, you cause named sets to become available.

• If you ever use the GETPIVOTDATA function to extract values from pivot tables, you can save a step and convert your pivot table to Cube formulas. Cut and paste these into any format desired.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.