Which of the following is a benefit that the Power Pivot data model gives Excel users over standard PivotTables?

Yes, you read the title of this post correctly, you can calculate the median and lots of other functions in Excel PivotTables besides the regular options.

If you’re a regular user of Excel PivotTables you might know you can change the summary function:

Just right-click inside of the PivotTable → Go to Summarize Values By → Select the summary function

Which of the following is a benefit that the Power Pivot data model gives Excel users over standard PivotTables?
Change summary function in PivotTable

If you click on More Options…, you can select other functions such as Standard Deviation and CountNumbers. But, what if you need something different such as the median? That’s when Power Pivot comes into play.

Power Pivot is an Excel built-in feature (for Excel 2013 and later) that allows you to significantly extend the capabilities of regular PivotTables. For example, with Power Pivot you can use information from multiple tables without having to join it into a single table. Also, you can use lots of summary functions that are not available in regular PivotTables (e.g. Median). Note: The median function is only available in Power Pivot for Excel 2016.

Check this 5 min video below to get more information about Power Pivot

To show how to calculate the median (or another measure) in PivotTables, I’ll use a sample dataset that contains shipping data. The dataset contains the following columns:

  • Order Number: ID of the order
  • Distribution Center: Location from where the order is shipped
  • Quantity: Number of items shipped in the order
  • Order Date: Date when the order was placed by the customer
  • Delivery Date: Date when the order was delivered to the customer

Download Data

Download the raw data from this link.

How to add the median to a PivotTable

The process to calculate the median (or any other function) in PivotTables is as follows:

Which of the following is a benefit that the Power Pivot data model gives Excel users over standard PivotTables?
Create a measure in Power Pivot

» Activate Power Pivot

  1. The activation of Power Pivot must be done once. These are the steps to do it:

» Load the data into Power Pivot

  1. Place the cursor on any cell within the range that contains the data
  2. Go to Insert → PivotTable
    Which of the following is a benefit that the Power Pivot data model gives Excel users over standard PivotTables?
    Insert Pivot Table
  3. Make sure the range selected is appropriate and check on Add this data to the Data Model.
  4. Click OK.
    Which of the following is a benefit that the Power Pivot data model gives Excel users over standard PivotTables?
    Add data to Excel Data Model

    Note: If you’re importing the data from an Excel Table, the Data Model will use the table’s name, otherwise, it will use the name Range for the table. Keep this in mind as it is import for the second example of this post.

» Create the desired measure

  1. Go to the Power Pivot tab → Click on Measures → New Measure
  2. Specify the name of the measure (e.g. Median Quantity)
  3. Enter the formula for the measure. For example, to calculate the median of a column called QUANTITY, enter the formula =MEDIAN([QUANTITY]). Notice the use of brackets to refer to columns.
    Which of the following is a benefit that the Power Pivot data model gives Excel users over standard PivotTables?
    Create a measure with the median
  4. In addition, you can specify the name of the table where the measure will be stored and a description for the measure.

» Use the measure in a PivotTable

  1. You can drag the desired fields and the measure to the PivotTable.

For example, the image below shows a PivotTable with the column Distribution Center in the rows area and the measure Median Quantity in the values area.

Which of the following is a benefit that the Power Pivot data model gives Excel users over standard PivotTables?
Add median to Pivot Table

Calculate the median from row-wise operations

Let’s say you want to calculate the median of the number of days passed from the Order Date to the Delivery Date. This date difference is called leadtime.

There are two ways of doing this:

  1. Create a Leadtime column in the table and then calculate the median of the Leadtime column
  2. Use the function MEDIANX to calculate the leadtime and the median in a single formula

I’ll explain how to use MEDIANX:

All Power Pivot functions ending in X are iterators. Iterator functions can perform an operation for each row of a table.

In general, the syntax for an iterator is:

=FUNCTIONNAMEX(Table, Operation)

In this case, we want to subtract the Order Date from the Delivery Date for each row of the table called Range and then we want to calculate the median of those results.

Therefore, the syntax would be:

=MEDIANX(Range, Range[Delivery Date] – Range[Order Date])

Challenge

Here’s a tough challenge for you:

Let’s say there’s a target of having a leadtime (delivery date – order date) of 15 days or less. How would you calculate the percent of orders that had a leadtime greater than 15 days for each of the distribution centers?

The PivotTable should look like this:

Which of the following is a benefit that the Power Pivot data model gives Excel users over standard PivotTables?
Pivot Table with proportion of late orders

If we pick distribution center B, 94.8% of the orders had a leadtime greater than 15 days.

HINT: Read about these Power Pivot functions: COUNTX, AVERAGEX, IF, COUNTROWS, FILTER, BLANK. You don’t need to use all of them to answer the question.

Write your solution in the comments section. By October 15, I’ll share a workbook with all the solutions posted and the names of the people who posted.

Please share this post so more people can benefit!

What are the benefits of power pivot tool?

Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.

What are the benefits of using Powerpivot table over a regular PivotTable?

The normal pivot version just lists fields within this single table or source that we're pointing to. Power Pivot allows us to access any of the fields in any of the tables in our data model, and then analyze them based on any relationships that we've defined. So that's the most obvious of the differences.

Which of the following provides the greatest advantage of using a PivotTable in Excel?

Pivot tables are useful with large amounts of data that would be time-consuming to calculate by hand. It flips and sums data in seconds and allows us to perform data analysis and generating reports like periodic financial statements, statistical reports, etc.

What is the advantage of using PivotTable in Excel?

A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail, and answer unanticipated questions about your data. A PivotTable is especially designed for: Querying large amounts of data in many user-friendly ways.