Power BI provides a wide range of DAX functions to perform advanced calculations and improve data presentation. AVERAGEX and ROUND are especially useful when working with row-level calculations and formatting numeric results.
AVERAGEX
AVERAGEX is an iterative DAX function that evaluates an expression for each row of a table and then returns the average of those calculated results. Unlike the standard AVERAGE function, AVERAGEX allows you to perform calculations at the row level before aggregation.
Syntax:
AVERAGEX(
<Table>,
<Expression>)
Calculating Base Dimensions Using AVERAGEX
Consider a table ProductHierarchy with the following columns:
Step 1: Create a New Measure
- Right-click on the Product table
- Select New measure
- Name the measure Base Dimension
Step 2: Define the AVERAGEX Measure
- AVERAGEX iterates through each row of the producthierarchy table
- For every row it calculates Length x Width
- It then returns the average of all calculated values
Step 3: Visualize the Result
You can add this measure to a Table, Matrix or Card visual to analyze average base dimensions across products or categories.
ROUND
After calculating averages results often contain multiple decimal places which may not be ideal for reporting or dashboards. The ROUND function helps format numeric results by controlling the number of decimal places displayed.
Syntax:
ROUND(
<Number>,
<Num_Digits>
)
Rounding the Base Dimension
To round the average base dimension update the measure as follows:
How ROUND function Works
- The first argument is the number or expression to be rounded
- The second argument specifies the number of decimal places
Common Rounding Options
- ROUND(..., 2): Rounds to two decimal places
- ROUND(..., 0): Rounds to the nearest whole number
- ROUND(..., -1): Rounds to the nearest multiple of 10
- ROUND(..., -2): Rounds to the nearest multiple of 100
Why Use AVERAGEX with ROUND
- Row-Level Accuracy: AVERAGEX ensures calculations are performed per row
- Cleaner Presentation: ROUND improves readability of numeric results
- Dynamic Behavior: Measures respond automatically to filters and slicers
- Efficient Modeling: No need for additional calculated columns
Difference Between AVERAGEX and ROUND
Here we compare AVERAGEX and ROUND function in Power BI
Features | AVERAGEX | ROUND |
|---|---|---|
Function Type | Iterator (X) function | Mathematical function |
Goal | Calculates the average of an expression evaluated for each row | Rounds a number to a specified number of decimal places |
Input Type | A table and an expression | A numeric value or expression |
Row-Level Evaluation | Evaluates expression row by row | No row-level evaluation |