Forum Discussion
Calculate average of values between two boundaries
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
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.
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?
Super UserAnonymous
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.
Thanks for this excellent explanation. I'm now one step further in understanding DAX!