Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
Solved! Go to 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
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
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 ) ) )
@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?
@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.
Thanks for this excellent explanation. I'm now one step further in understanding DAX!
@Anonymous
HI,, Please Format the column as Decimal with 2 decimal points and check the results then
Do let me know
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |