Quick answer
Use this DAX pattern as a starting point and rename the table, column, and measure references for your model.
Average Order Value = DIVIDE([Total Sales], DISTINCTCOUNT(Sales[Order ID]), 0)When to use it
Use Average Order Value measure when the calculation belongs in a Power BI semantic model and needs to respond to slicers, filters, relationships, and report context.
Step-by-step
- Open Power BI Desktop and select the table where the measure should live.
- Create a new measure and paste the DAX example.
- Replace table and column names with your actual model names.
- Test the measure in a table visual before using it in cards or charts.
- Check the total row because DAX totals are calculated in their own filter context.
Excel comparison
The Excel equivalent is usually a worksheet formula or PivotTable calculation. Use DAX when the calculation should respond to report filters and relationships.
Common mistakes
- Using a calculated column when a measure is better.
- Ignoring filter context and expecting row-by-row Excel behavior.
- Not having a proper date table for time intelligence formulas.
- Using division without DIVIDE, which can create divide-by-zero errors.