Multiply a filtered value by an unfiltered one in sumx

[FrancoIVM]

Hi,

I have a measure with the sales of the month for different products and I want to multiply it by another measure that contains the last purchase value of these products, the problem is that the sales are associated with (for example) a warehouse X1 and the purchase value is associated with where they were received, let's say is warehouse X2. So when I want to see the [sales]* [purchase value] it is blank because there is no purchase value associated with X1 (when im filtering by X1).

What I did was that the measure [purchase value] looked for the purchase value in all the warehouse with ALL as follows:

[Purchase value] = CALCULATE(
LASTNONBLANKVALUE('table'[date],
SUM('table'[values])),
ALL('table'[warehouse]))

That worked to show me the last purchase value of each product, but when multiplying it in the form:
[sales] * [purchase value]
Totals are multiplied because the calculation is not being done row by row.
But doing it with SUMX in the way:
SUMX ('table', [sales] * [purchase value])
The result is blank again as if [purchase value] was not looking up the values in all warehouses.

You could use ALL ('table' [warehouse]) like this:
CALCULATE (SUMX ('table', [sales] * [purchase value]), ALL ('table' [warehouse]))
But I'm interested in the sales being filtered through the selected warehouse.

So I wonder if there is a way that the [purchase value] is related to all the warehouse or if someone else comes up with another solution.

[amitchandak · Accepted solution]

@FrancoIVM , if they are from the same table calculation should be done at the row-level

you can get a price like

[Purchase value] = CALCULATE(
LASTNONBLANKVALUE('table'[date],
SUM('table'[values])),
filter('table' , 'table'[warehouse] = earlier('table'[warehouse])))

or

[Purchase value] = CALCULATE(
LASTNONBLANKVALUE('table'[date],
SUM('table'[values])),
filter('table' , 'table'[item] = earlier('table'[item])))

or have the level to use values

CALCULATE (SUMX (values('table'[?]), [sales] * [purchase value]), ALL ('table' [warehouse]))

[FrancoIVM]

Thanks you,

The last alternative works for me, 

CALCULATE (SUMX (values('table'[?]), [sales] * [purchase value]), ALL ('table' [warehouse]))

The ALL ('table' [warehouse]) was'nt necessary, when i used the Vale(table[items]) instead the table, the filters works fine.

I was a lot of time on this, so really ty!