A DAX formula in Excel is an expression used in the Excel Data Model to create a measure or calculated column; it is not a formula you enter into an ordinary worksheet cell. For example, a measure that adds a table’s sales amounts can use =SUM(Sales[Amount]) once the Sales table is in the Data Model.
What DAX Means in Excel
DAX stands for Data Analysis Expressions. It is a formula language for working with related tables and summarizing data in a model. Excel uses DAX through Power Pivot and the Data Model, the same general modeling technology used by Power BI. It is useful when a report needs calculations that respond to PivotTable filters, or when data is spread across several related tables.
DAX is not a replacement for Excel’s regular worksheet formulas. Typing a DAX expression into a cell will not create a Data Model measure; a cell expects a worksheet function such as SUM or AVERAGE. DAX formulas belong in a model as either:
- Measures: Calculations evaluated when a PivotTable or other model report requests a result. A measure can change with filters, such as the selected year or region.
- Calculated columns: Values calculated separately for each row in a model table. They are stored in the model and can be used as fields in reports.
For a simple sum in a worksheet, ordinary Excel formulas are usually simpler. DAX becomes valuable when the calculation needs model relationships, report context, or reuse across multiple PivotTables.
How DAX Formulas Work
DAX expressions use functions, operators, table names, and column names. A column reference commonly takes the form TableName[ColumnName], while a measure is typically referenced by its name in square brackets. DAX formulas begin with an equals sign. In Excel’s measure dialog, you provide the measure name separately from its expression.
The key idea is context. A calculated column is evaluated row by row, so a formula can use values from the current row. A measure is evaluated for the current filter context: the filters supplied by a PivotTable’s rows, columns, slicers, and report filters. The same measure can therefore return the overall total, a total for one region, or a total for one product without changing its expression.
For example, SUM adds the values in a column. CALCULATE evaluates an expression after applying a specified filter. These functions are often combined: SUM supplies the calculation, while CALCULATE changes the context in which it is calculated. Understanding that distinction helps explain why a DAX measure may show different results in different parts of a PivotTable.
Prepare Excel Data for DAX
Start with clean tabular data: one header row, one record per row, and a consistent data type in each column. Convert the range to an Excel table with Insert > Table, then give it a clear name in the Table Design tab. For the examples below, the model contains a table named Sales with columns Amount, Region, Quantity, and UnitPrice. Assume four records: West, 100, 2, and 50; East, 200, 4, and 50; West, 150, 3, and 50; and East, 50, 1, and 50. Amount is the total value of each record, so the overall amount is 500.
In Excel for Microsoft 365 on Windows, Power Pivot authoring depends on the Excel edition and whether the add-in is available. If it is available but not visible, enable it through File > Options > Add-ins. In the Manage box, choose COM Add-ins, select Go, and check Microsoft Power Pivot for Excel. To create a model, use Insert > PivotTable and select the option to add the data to the Data Model, or load data into the model through Power Pivot.
Open the model using Power Pivot > Manage. From there, you can create a measure using the New Measure command, or add a calculated column in the table’s grid. Ribbon labels can vary slightly between Excel builds. Excel for the web does not provide the same Power Pivot model-authoring workflow, and Excel for Mac does not offer the Windows Power Pivot authoring add-in. If you cannot find the commands, check your Excel edition and platform.
Useful DAX Formula Examples
In the examples, Sales is the Data Model table described above. Enter a measure expression in the measure dialog and assign it a name such as Total Sales. Because measures are evaluated in context, the result can be broken down by Region or another field in a PivotTable.
Add all sales amounts: Assume the Data Model has a table named Sales with an Amount column containing 100, 200, 150, and 50. The measure below returns 500 when no filters are applied.
=SUM(Sales[Amount])
Calculate sales for one region: Assume the same Sales table has a Region column, with West on the records for 100 and 150. This measure returns 250 for West when the model has no other filters affecting the result.
=CALCULATE(SUM(Sales[Amount]),Sales[Region]="West")
CALCULATE applies the West condition while evaluating the sum. In more complex reports, other filters may also affect the result; DAX filter behavior is part of the context that makes measures adaptable.
Count records: Assume Sales contains the four records listed above. This measure returns 4 for the full table and can return a smaller count when a PivotTable filters the table.
=COUNTROWS(Sales)
Calculate a value for each row: Assume the Sales table has Quantity and UnitPrice columns as specified above. Enter this as a calculated column, not as a measure. It returns 100, 200, 150, and 50 for the four rows respectively.
=Sales[Quantity]*Sales[UnitPrice]
That row calculation matches the Amount values in this example, but a separate calculated column is unnecessary if Amount already exists. A measure is generally preferable for a total or ratio because it can respond to report filters and does not store a repeated result for every row.
Calculate average value per unit: Assume the same four records, with Amount totaling 500 and Quantity totaling 10. This measure returns 50, calculated as total amount divided by total quantity. DIVIDE handles a zero denominator more safely than using the division operator directly; its optional third argument supplies a result for division by zero.
=DIVIDE(SUM(Sales[Amount]),SUM(Sales[Quantity]),0)
This is a weighted average amount per unit, not the unweighted average of the four row-level prices. That distinction matters when records have different quantities.
Measures Versus Calculated Columns
Choose a measure when the answer should be recalculated as report filters change: totals, counts, ratios, and year-to-date summaries are common examples. Measures do not add a stored result to every source row. They are usually the right starting point for PivotTable calculations.
Choose a calculated column when each row needs a persistent category or value, such as a row-level product of quantity and price, or a label derived from other columns. Calculated columns can be used as PivotTable fields and can participate in relationships, but they increase model size and are evaluated for every row. A common misconception is that a calculated column and a measure are interchangeable because both use DAX. Their evaluation context and purpose differ, so selecting the right type is part of designing the calculation.
DAX Compared with Excel Formulas and Power Query
Worksheet formulas operate in cells and are convenient for calculations beside a small, directly visible range. They can use cell references and update as the sheet changes. DAX operates on Data Model tables and is designed for analysis across tables and report filters. It is not necessary to move every spreadsheet calculation into DAX.
Power Query is different again. It imports, cleans, and reshapes data before it is loaded into a worksheet or Data Model. Use Power Query to remove unwanted rows, split or combine columns, and standardize source data. Use DAX for calculations over the model, especially measures that need to respond to PivotTable selections. A workflow can use all three: Power Query to prepare data, the Data Model to relate tables, and DAX to calculate report values.
Common DAX Problems
- Trying to enter DAX in a cell: DAX expressions must be created in the Data Model; worksheet cells use Excel formulas.
- Missing or misspelled table names: Check the model’s table and column names. References such as Sales[Amount] must match the model, not necessarily the original worksheet range name.
- Using a measure where a column is needed: A measure returns a result for a report context; it is not a row-by-row field that can be used like an ordinary column.
- Unexpected totals: A measure is recalculated in the total row’s context. Its total may not equal the sum of the visible row results, especially for ratios or distinct counts. Check what the measure is designed to calculate rather than assuming every total is an addition.
- Wrong data types or missing relationships: Text stored where numbers are expected can interfere with arithmetic, and unrelated tables may not filter one another as intended. Verify column types and model relationships.
- Unavailable Power Pivot commands: Feature availability depends on Excel edition and platform. Confirm that you are using desktop Excel for Windows with an edition that includes Power Pivot authoring.
Frequently Asked Questions
Can I use DAX formulas in regular Excel cells?
No. DAX formulas are used in the Excel Data Model, not entered into ordinary worksheet cells. Use Excel functions in cells, or create a Data Model measure or calculated column for DAX.
How do I create a DAX formula in Excel?
Put the source table in the Data Model, open the model with Power Pivot > Manage, and create a measure or calculated column. For a measure, enter a name and a DAX expression such as =SUM(Sales[Amount]).
Is DAX the same as Excel formulas?
No. Both use functions and expressions, but they run in different places and use different evaluation rules. Worksheet formulas calculate in cells; DAX calculates over model tables and responds to model filter context.
Does DAX work in Excel for Mac?
Excel for Mac does not include the Windows Power Pivot authoring add-in, so it does not provide the same way to create DAX measures and model calculations. Availability of other Data Model features may differ by version.
Should I use a DAX measure or a calculated column?
Use a measure for a result that should change with PivotTable filters, such as total sales. Use a calculated column for a value that belongs to each individual row and needs to be available as a model field.
Related
- Excel Conditional Formatting Rules: How to Use Them
- Conditional Formatting Scatter Plots in Excel
- Conditional Formatting Percentages in Excel: A Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel: Sum VLOOKUP Values Across Multiple Matching RowsTo total every row that matches a lookup value, use SUMIF, not a regular VLOOKUP: with the matching…
- Excel Sum by Date: Formulas for Daily and Monthly TotalsTo sum values for a date in Excel, use SUMIF with the date column as the criteria range…
- Round Excel Numbers Without a Formula: Display or ChangeYes. In Excel for Microsoft 365 on Windows, use a number format to display fewer decimal places without…
- Split Text in Excel Without a Formula: Easy MethodsYes. In Excel for Microsoft 365 on Windows, split text without a formula using Data > Text to…
- WEEKDAY Function in Excel: Find the Day of the WeekExcel’s WEEKDAY function returns a number for the day of the week represented by a date. If cell…
- VLOOKUP Examples for Beginners: A Practical Excel GuideIn Excel, VLOOKUP finds a value in the first column of a range and returns related information from…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.