- Downloading and Installing PowerPivot
- Getting your Data into PowerPivot
- Linking an Excel Table to PowerPivot
- Importing Data into PowerPivot
- Adding DAX Calculations to the Grid
- Creating Pivot Tables from PowerPivot
- Defining a Relationship Between Worksheets
- Using Automatic Relationship Detection
- Using Explicit Relationships
- Using the RELATED Function in the PowerPivot Grid
- Adding Slicers to Drive All Pivot Elements
- Adding DAX Calculations to the PivotTable
- Entering a Measure
- Using the X Functions
- Using CountRows and Distinct
- PowerPivot Is a Home Run
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Using the X Functions
Traditionally, regular pivot tables would perform the calculation on each row of the original data set and then sum or count the results. Suppose that you wanted to analyze the range of sales in Illinois for Q1. A traditional pivot table could calculate the Min and the Max, but any attempt to calculate the MAX-MIN would fail since the calculation happens on a row-by-row basis. (For any sale, the Max(Revenue) and Min(Revenue) are identical, so the range is always zero.
DAX provides seven statistical functions with an extra X at the end. SUMX will sum an expression. MINX will find the minimum value of an expression. For the previous active cell, creating a measure of =MINX(Demo,Demo[Revenue]) will cause PowerPivot to look at all the Illinois records in Q1 and to find the minimum of all of those records. This is an important distinction from the regular pivot table, which performs all calculated field formulas on individual rows instead of groups of rows.
The six X functions are AVERAGEX, COUNTX, COUNTAX, MAXX, MINX, and SUMX.