How to Calculate a Dynamic Truncated Mean in Power BI Using DAX

Source: Dev.to

Why You Need a Truncated Mean

In data analysis, the standard AVERAGE function is a workhorse, but it has a significant weakness: it is highly susceptible to distortion from outliers. A single extreme value, whether high or low, can skew the entire result, misrepresenting the data’s true central tendency.

This is where the truncated mean becomes essential. It provides a more robust measure of average by excluding a specified percentage of the smallest and largest values from the calculation.

While modern Power BI models have a built‑in TRIMMEAN function, this function is often unavailable when using a Live Connection to an older Analysis Services (SSAS) model. The following DAX pattern replicates this functionality and remains fully dynamic, responding to all slicers and filters in your report.

The DAX Solution for a Dynamic Truncated Mean

This measure calculates a 20 % truncated mean by removing the bottom 10 % and top 10 % of values before averaging the remaining 80 %.

You can paste this code directly into the New Measure formula bar.

Trimmed Mean (20%) = 
VAR TargetTable = 'FactTable'
VAR TargetColumn = 'FactTable'[MeasureColumn]
VAR LowerPercentile = 0.10 // Defines the bottom 10% to trim
VAR UpperPercentile = 0.90 // Defines the top 10% to trim (1.0 - 0.10)

// 1. Find the value at the 10th percentile
VAR MinThreshold =
    PERCENTILEX.INC(
        FILTER( 
            TargetTable, 
            NOT( ISBLANK( TargetColumn ) ) 
        ),
        TargetColumn,
        LowerPercentile
    )

// 2. Find the value at the 90th percentile
VAR MaxThreshold =
    PERCENTILEX.INC(
        FILTER( 
            TargetTable, 
            NOT( ISBLANK( TargetColumn ) ) 
        ),
        TargetColumn,
        UpperPercentile
    )

// 3. Calculate the average, including only values between the thresholds
RETURN
CALCULATE(
    AVERAGEX(
        FILTER(
            TargetTable,
            TargetColumn >= MinThreshold &&
            TargetColumn <= MaxThreshold
        ),
        TargetColumn
    )
)

Deconstructing the DAX Logic

The formula works in three distinct steps, all of which execute within the current filter context (e.g., whatever slicers the user has selected).

1. Define Key Variables

• TargetTable & TargetColumn – Assign the table and column names to variables for clean, reusable code. Replace 'FactTable'[MeasureColumn] with the appropriate column from your model.
• LowerPercentile / UpperPercentile – Define the boundaries. 0.10 and 0.90 trim the bottom 10 % and top 10 %. To trim 5 % from each end (a 10 % total trim), use 0.05 and 0.95.

2. Find the Percentile Thresholds

• MinThreshold & MaxThreshold – Store the actual values that correspond to the percentile boundaries.
• PERCENTILEX.INC – Used as an iterator so we can first FILTER the table.
• FILTER(…, NOT(ISBLANK(…))) – Ensures percentiles are calculated only for rows where the target column is not blank, preventing BLANK() values from skewing the calculation.

The result is that MinThreshold holds the value of the 10th percentile (e.g., 4.5) and MaxThreshold holds the value of the 90th percentile (e.g., 88.2) for the currently visible data.

3. Calculate the Final Average

• RETURN CALCULATE(…) – Makes the measure dynamic, respecting any filters applied by slicers or visuals.
• AVERAGEX(FILTER(…)) – Iterates over the filtered table, calculating the simple average of TargetColumn for rows that meet the trim criteria.
• The inner FILTER keeps only rows where TargetColumn is ≥ MinThreshold AND ≤ MaxThreshold.

Conclusion

By implementing this DAX pattern, you create a robust, dynamic, and outlier‑resistant KPI. The measure provides a more accurate picture of your data’s central tendency and will automatically re‑calculate as users interact with your Power BI report.