Home > Articles > Home & Office Computing > Microsoft Applications

  • Print
  • + Share This
From the author of Using the X Functions

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.

  • + Share This
  • 🔖 Save To Your Account