Filter based on a calculated value

[Machinist26]

I'm not sure why I can't make this work, but I'm having a terrible time trying to filter based on a calculated field. The intent:

To calculate the average unit price from a sales data set. I have this part. Then to report the revenue from sales where the unit price was less than the previously calculated average. Basically a glorified SUMIF.

Sample data set:

Unit Price Volume Sales
0.221517426 90.48281858 20.04352108
0.150546562 74.83994718 11.26689675
0.125449802 65.50048886 8.217023344
0.652954905 0.614463156 0.401216732
0.895887196 21.10741268 18.90986076
0.745563837 13.42812059 10.01152111


Calculated field "average" is defined as:

SUM(Table1[Sales])/SUM(Table1[Volume])

And it works. No problem.

Now I'm trying to use FILTER() to filter out ever line from the source data table whose unit price is less than "Average":

SUMX(filter(Table1,Table1[Unit Price]<[Average]),Table1[Sales])


And the numbers I get don't make sense. If I replace the measure with a constant, the resulting value is correct:

SUMX(filter(Table1,Table1[Unit Price]<0.39279385),Table1[Sales])



Where am I going wrong?

 

[scottsen]

SUMX(FILTER(Table1) ...)

Is going to iterate over the table.. evaluating one row at a time. So the [average] of the 1 row... is just the value of the one row.

Likely fix is just to use SUMX(FILTER(ALL(Table1) ...) ?

 

[Machinist26]

But [Average] was already defined for the entire relevant data set. It's .3927. Are you saying that the way I've written my formula will actually recomputed the average for every row?

 

[scottsen]

Say you have:
Average := SUM(Table1[Sales])/SUM(Table1[Volume])

That is a measure that can be evaluated in any context, including a single row. If you had explicitly done:
Average := CALCULATE(SUM(Table1[Sales])/SUM(Table1[Volume]), ALL(Table1))

Then you would be good.