Calculate average of values between two boundaries

[Anonymous]

I need to calculate the average of values in a column for those values that are >= a lower limit and <= an upper limit.

These limits are defined as measures (lower and upper). I thought that was pretty simple

EHT =
CALCULATE (
    AVERAGE ( 'STA Core'[Total TouchTime for the record] ),
    FILTER (
        'STA Core',
        'STA Core'[Total TouchTime for the record] >= [lower]
            && 'STA Core'[Total TouchTime for the record] <= [upper]
    )
)

However this gives me the overall average, why is it ignoring the filter? After doing some research and reading I thought I got it. User AVERAGEX instead:

EHT =
AVERAGEX (
    VALUES ( 'STA Core'[Total TouchTime for the record] ),
    CALCULATE (
        AVERAGE ( 'STA Core'[Total TouchTime for the record] ),
        FILTER (
            'STA Core',
            'STA Core'[Total TouchTime for the record] <= [upper]
                && 'STA Core'[Total TouchTime for the record] >= [lower]
        )
    )
)

But again, it doesn't honor the filters... what am I doing wrong?

When I found this solution by @OwenAuger I thought he will be my hero for the day, but when adapting it to my data model, it gives me impossibly weird results. Is it perhaps that you cannot use the same column you want to calculate the average as filter criteria column?

[Zubair_Muhammad · Accepted solution]

@Anonymous

Give this a shot

EHT =
VAR myupper = [upper]
VAR mylower = [lower]
RETURN
    AVERAGEX (
        VALUES ( 'STA Core'[Total TouchTime for the record] ),
        CALCULATE (
            AVERAGE ( 'STA Core'[Total TouchTime for the record] ),
            FILTER (
                'STA Core',
                'STA Core'[Total TouchTime for the record] <= myupper
                    && 'STA Core'[Total TouchTime for the record] >= mylower
            )
        )
    )

[Zubair_Muhammad · Accepted solution]

@Anonymous

Give this a shot

EHT =
VAR myupper = [upper]
VAR mylower = [lower]
RETURN
    AVERAGEX (
        VALUES ( 'STA Core'[Total TouchTime for the record] ),
        CALCULATE (
            AVERAGE ( 'STA Core'[Total TouchTime for the record] ),
            FILTER (
                'STA Core',
                'STA Core'[Total TouchTime for the record] <= myupper
                    && 'STA Core'[Total TouchTime for the record] >= mylower
            )
        )
    )

[Anonymous]

@zubairthis seems indeed to do the trick, but why is it always returning an integer?

Here the results when I do the calculation in Excel (manually removing the entries above upper and below lower threshold):

Percentile 75	30
Percentile 25	20
Outliers down	20-(30-20)*1.5	5
Outliers up	30+(30-20)*1.5	45
Average RTT	average including outliers	28.2
EHT	average excluding outliers	25.8

And here the results in Power BI. Quartile and upper/lower thresholds are OK, but your suggestion returns 26 instead of 25.8. Why is that? I check format for decimals, but no, that's not the point.

I still would like to understand why my formulas don't respect the filter clause (they both return the unfiltered average), why @OwenAuger's approach (which looks like the perfect approach) is returning a value that simply can't be (as we're in this case removing many more outliers above the threshold than below, the resulting average can impossibly higher than the unfiltered average). And finally what I've already stated above. We your formula is returning 26 and not 25.8?

[OwenAuger · Accepted solution]

@Anonymous

The core problem which @Zubair_Muhammad fixed in this example is that if you need to reference the lower/upper bounds within FILTER (or any other iterator) you must compute those bounds first and store them in variables, so that their values remain fixed.

In your original formula, [lower] and [upper] measures were used within the row context created by FILTER, which meant they were computed in a filter context corresponding to each individual row of the table being iterated (due to context transition). Since it looks like [lower] and [upper] are defined using lower/upper quartiles, this would have meant that the value in every row ended up falling between [lower] and [upper] when computed in the context of that row, giving you an unfiltered result.

[Anonymous]

Thanks for this excellent explanation. I'm now one step further in understanding DAX!

[Zubair_Muhammad]

@Anonymous

HI,, Please Format the column as Decimal with 2 decimal points and check the results then

Do let me know