This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Using MAXX, MINX, and SUMX
Problem: I want to calculate a range of Max(Price) - Min(Price) in a pivot table. However, when I do that using a calculated field in a pivot table, everything comes out as zero.
Figure 980 The range is always zero?
Here is what is happening: The calculated field goes to each row in the original data set. It calculates the MAX(D2) which is 25.74. Then it calculates the MIN(D2). This also is 25.74. The MAX(D2)-MIN(D2) is zero.
PowerPivot and DAX provide a series of functions that will solve this problem. SumX stands for Sum Expression. It will sum the numeric values that results from evaluating an expression for each row of a table. DAX also offers AverageX, CountX, CountAX, MinX, and MaxX. Here is how to use them to solve this problem.
1. Go to your original data set.
2. Press Ctrl+T to define it as a table.
3. On the PowerPivot ribbon tab in Excel, use Create Linked Table.
4. You will wind up in the PowerPivot window for a moment, Click the XL icon in the PowerPivot QAT to return to Excel.
Figure 981 Spend two seconds in PowerPivot, then back to Excel.
5. Click the Pivot Table icon in the PowerPivot tab. Choose the first choice for a single pivot table.
6. Add Company to the row labels area.
7. In the PowerPivot tab, click New Measure
8. Define a measure for Min Price. The formula is MINX(Table1, Table1[Price]).
Figure 982 Define a measure to find the minimum for each company.
9. Repeat steps 7-8 for Max Price. = MaxX(Table1,Table1[Price])
10. Click New Measure to define Range. The formula for this measure can refer to other measures that have already been defined. While you would like to refer to =MaxP-MinP, you have to fully qualify the name using the table syntax. Start typing =T, then choose Table1[MaxP] from the dropdown list. Click that choice then press tab. Type a minus sign and then T again. Choose Table1[MinP] from the dropdown. Your final formula is =Table1[MaxP]-Table1[MinP].
Result: a pivot table that does the Max(All Rows)-Min(All Rows) calculation for each company.
Figure 983 Three DAX measures perform the needed calculation.
Additional Details: You could have built this using a single measure with the formula: =MaxX(Table1,Table1[Price])-MINX(Table1,Table1[Price]).
The beauty in MINX and MAXX is that they do respect the filters applied to the cell in the pivot table from the row labels, column labels, and slicers. If you added month as a column field, to calculate the March range for Apple, the PowerPivot calculation engine will essentially filter the data to only Apple, only March, the apply the MAXX and MINX to the results returned from those filters. This figure attempts to illustrate the calculation that happens in memory in PowerPivot.
Figure 984 For one cell in the pivot table, PowerPivot find the rows that match the filters, takes the Max and Min of those rows.