Sum at the correct level

[ells]

ok,
So this is more of a quaestion about how to move up a level in a dimension and maintain the sum of the components rather than doing the calculation at the highest level.

so I have a

table 'expired' that shows expired stock at product level (one row per product
table 'stock' that shows available stock at product level (multiple rows per product)
Measure [expired] = SUM('expired'[Qty]

to get the amount of available product I go

VAR NumberofRows =SUMX('stock',1)
VAR AvailableStock = SUMX('stock','stock'[Quantity] - DIVIDE( [expired], NumberOfRows)

I have multiple scenarios like this where the calculation must be done at the base level and summed up. How do I do this?

Thanks

E

[halfglassdarkly]

If I understand you correctly the issue is the need to filter your sumx at product level?

e.g.

Var PID = SelectedValue('stock'[Product ID])

Var AvailableStock = SUMX(FILTER('stock','stock'[Product ID]=PID),'stock'[Quantity])

Var ExpiredStock = SUMX(FILTER('expired','expired'[Produt ID]=PID),'expired'[Quantity])

RETURN

//I'm not entirely sure what your example is aiming to do using Divide? If you were simply subtracting expired stock from the stock total it would look like this

Availablestock - ExpiredStock

See also the example in the Microsoft documentation for SUMX: https://learn.microsoft.com/en-us/dax/sumx-function-dax

[ells]

Thanks,
Will look at that. SumX has not quite worked out as I hoped for. It is close but not spot on.