Using DAX formulas in Pivot Tables

When calculating data in an Excel pivot table, you're not limited to the listed options in the value field settings under the summarize value field by selection. There are lists of calculations including the sum, average, max, and minimum values for data in the pivot table. You can also input DAX, or Digital Analysis Expression, formulas in the table, which will return a table as a result. A pivot table doesn't include median values in the list of preset values, but you can enter a DAX formula which will add it to the table.

Follow these steps explained by Bill Jelen here.

1. When selecting the data range for the pivot table select the option to add the data to the Data Model.

2. With the data set up this way, you'll see that in the PivotTable Fields box, you have the option to 'Add Measure' when you right click on the Range.

3. Give the new measure a name, and then enter MEDIAN as the formula selecting the field you want to analyze.

4. Set the appropriate number format, and use the Check DAX Formula tool to confirm that the formula will function.

5. The MEDIAN DAX formula is now listed as a new field for the Pivot table can be added to the displayed values.

Note that in this data set, the zero values are not an error. This is just showing that in each of the selected years most players had no stolen bases - except for 1908, when a majority of players had at least one stolen base.