Spreadsheets

DAX basics: SUM and CALCULATE for a category total in Power Pivot

Learn DAX basics for expense data: write Total Spend with SUM, build Food Spend with CALCULATE and a category filter, add both to a pivot, and know why.

Editorial illustration for DAX basics: SUM and CALCULATE for a category total in Power Pivot

In DAX, Total Spend is a measure written as SUM over the amount column, and Food Spend is a second measure that wraps Total Spend in CALCULATE with a category filter. Add both to a pivot and each will recalculate for every month or row you place beside them.

How do I write the Total Spend measure?

Assume a table named Transactions with an Amount column and a linked table named Categories with a Category column. In the Power Pivot tab choose Measures, then New Measure, name it Total Spend, and enter this formula:

  • Total Spend = SUM(Transactions[Amount])
  • If you type measures in the calculation area under the data grid in the Power Pivot window, the same measure is written as Total Spend:=SUM(Transactions[Amount]).

Set the number format to Currency or a number with thousands separators. That is all a plain total needs. Drop it into a pivot Values area with Month in Rows and you get the same answer as a normal sum.

How do I get Food Spend with CALCULATE?

CALCULATE takes an expression and then one or more filters that change what the expression sees. Create a second measure:

  • Food Spend = CALCULATE([Total Spend], Categories[Category] = "Food")

Reading it aloud: work out Total Spend, but only for rows where the category is Food. Note that Food is in double quotes because it is text, and the existing measure name goes in square brackets.

Now put Month in Rows and both measures in Values. Say April has 14,000 rupees of total spend, of which 4,200 is Food. The pivot shows 14,000 beside 4,200 on the April row, and each later month shows its own pair. Add a third measure to compare them:

  • Food Share = DIVIDE([Food Spend], [Total Spend])

DIVIDE returns blank instead of an error when the total is zero. Format it as a percentage. For April it gives 4,200 divided by 14,000, which is 0.30, shown as 30%.

What is the difference from a calculated column?

AspectCalculated columnMeasure
When it is worked outRow by row when data refreshesOn the fly for each pivot cell
Where it livesStored in the table, takes memoryNot stored, takes no row space
Typical useLabels, flags, sort keysTotals, shares, comparisons
Reacts to pivot filtersNo, one value per rowYes, recalculated per cell

In short: use a column for something you want to slice by, and a measure for something you want to add up.

Which mistakes should I watch for?

  • Using Transactions[Amount] as a column inside a measure without an aggregation. A bare column reference needs SUM or another function around it.
  • Straight quotes versus curly quotes. If you paste a formula from a document, retype the quotation marks.
  • Filtering on a column from an unlinked table. The filter has no effect unless a relationship connects the two tables.
  • Misspelled categories. If the table says Groceries, filtering for Food returns blank, not an error.

Where do I go from here?

  1. Add one measure per question you ask often, for example Travel Spend or Weekend Spend.
  2. Replace the typed category with a slicer once you want a switchable view.
  3. Learn a date table next so that year to date and last year comparisons work.

How do I put the measures into the pivot?

  1. Insert a PivotTable from the data model.
  2. Drag Calendar Month, or any other field, to Rows.
  3. Tick Total Spend, Food Spend and Food Share in the field list under the table where you created them.
  4. Add a slicer on Categories Group if you want to flip between views. Measures such as Food Spend ignore a Category slicer unless you designed them to, so test it.

If you notice you are writing a measure for each category, a report builder that filters and groups by category may be quicker; SpendixAI has one over imported transactions, plus ready-made reports.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports