Issue with evaluation context/context transition

[Sergii24]

Dear all,

I've got lost a bit in all types of filters and transitions of Power BI and would appretiate a lot your help!

In my dataset I have columns for Stock and Orders in items that should be delivered. The objective is to create a measure that will evaluate how many  items I can deliver, so the logic is the following: Match = IF (Stock>Orders) then Orders else Stock (example: I have 10 items of product A and orders for 15 items, so Match = 10 (statement IF (10>15) is FALSE).

This calculation should be done on row level, therefore I've created the following Base Measure to calcualte Orders and Stock:

Stock Qty = 
    SUMX( 
        Stock,
        Stock[Stock Qty] 
    )

Open Orders = 
    SUMX( 
        'Open Orders',
        'Open Orders'[Quantity] 
    )

As well as the formula for Match Measure:

Match (Stock) = 
VAR _NetStock = [Stock Qty] - [Open Orders]
VAR _Result =
    IF(
        _NetStock >= 0,
        [Open Orders],
        [Stock Qty]
    )
RETURN _Result

It works well on the lowest level of the granularity, but fails on aggregations. I expect 20 as the result for Herarchy Level 3, instead the result is 27.

Any ideas what do I miss?
Thank you very much!

[Fowmy · Accepted solution]

@Sergii24 

Not clear about the model and the relationship. Try an approach like below

Match (Stock) =

SUMX(
    VALUES(Stock[Items]) 
    VAR _NetStock = [Stock Qty] - [Open Orders]
    VAR _Result =
    IF(
        _NetStock >= 0,
        [Open Orders],
        [Stock Qty]
    )
    RETURN _Result
)

[Sergii24]

@Fowmy , thank you very much! It looks like that outer SUMX is what was missing!

In my understanding once I wrote 2 separate measures for "Stock Qty" and "Open Orders" I was ready to use them together in a new, 3rd measure, having already a row context, which is wrong.
The correct way, instead, is to have another outer SUMX even in my third variable ("Match Stock") that is introducing row context for this new measure for the first time!

Thanks a lot for filling this gap in my knowledge! 🙂

[PaulDBrown]

Can you please provide a sample dataset or PBIX file? Thanks!