Measure doesn't sum up when referring to other measures

[yoonjo]

Item number	Division	Stock	PO from DC	Stock Calc
12345678	DC	5		5
12345678	A	30	20	30
12345678	B	50
12345678	C	15
Total		100	20	100

Hello Community,

I would like to have a table(or matrix) that calculates a total stock quantity if division is DC or the division has PO from DC. 

I accordingly created measures as below.

The result in a row level is correct. But the total simply picks up the total of "Stock" column, not re-calculating within the "Stock Calc" column. 

Could someone please advise how to populate the correct total?

Stock = SUM ( 'Stock'[On hand approved] ) - SUM ( 'Stock'[Safety stock])

PO from DC = CALCULATE(SUM('Purchase orders'[Quantity]), 
            FILTER('Purchase orders', 'Purchase orders'[Order category]="251"),
            Supplier[Supplier name]="DC")

Stock Calc = 
SWITCH(TRUE(),
        SELECTEDVALUE(Division[Division]) = "DC", [Stock],
        [DC PO] > 0, [Stock],
        BLANK()
        )

[Anonymous]

Hi, @yoonjo 

Thanks for the reply from johnt75 and Greg_Deckler. You can refer to their suggestions to modify your dax. If it doesn't solve your problem, I noticed that the MEASURES you have given have some tables and fields that are not presented, such as Purchase orders table, Quantity field. In order to reproduce your issue, can you share the pbix file without sensitive data for testing. Feel free to help.

Best Regards,
Yang
Community Support Team

[johnt75]

In the total row it is calculating the value of PO from DC, which is 20, and because that is > 0 it is calculating the [Stock] measure, again in the context of the total row.

You need to iterate over the item numbers so that you're performing the calculation in the correct context.

Stock Calc =
SUMX (
    VALUES ( 'Stock'[Item Number] ),
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( Division[Division] ) = "DC", [Stock],
        [DC PO] > 0, [Stock],
        BLANK ()
    )
)

[Greg_Deckler · Accepted solution]

@yoonjo First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[yoonjo]

Thank you Greg. The issue is solved with HASONEFILTER and referring to a _table variable

Stock Calc = 
VAR _table = ADDCOLUMNS(SUMMARIZE(.....), "Stock Total", CALCULATE(SUM('Stock'[On hand approved]) - SUM('Stock'[Safety stock])))
RETURN
SWITCH(TRUE(),
        NOT(HASONEFILTER(Division[Division])), SUMX(_table, [Stock Total]),
        SELECTEDVALUE(Division[Division]) = "DC", [Stock],
        [DC PO] > 0, [Stock],
        BLANK()
        )