SUMX with 2 variables

[DLU]

Dear community, 

I've already posted this once, but I didn't receive any response after sharing my dataset so I'm trying it again. 

I'm currently struggling with a DAX calculation. I've created the following DAX measure: 

Total Trans QTY LTM =

VAR _Date = MAX('Date'[Date])

VAR _Startdate = DATE(YEAR(_Date)-1;MONTH(_Date);DAY(_Date))

VAR _BOMitem = IF(SELECTEDVALUE('ITEMID BOM'[BOM version.BOMID])="Blank";1;0)

VAR _Workorder = FIND(SELECTEDVALUE('INVENTTRANS OUT MERGED'[REFERENCEID]);"WO";1;0)

VAR _Table = FILTER('INVENTTRANS OUT MERGED';'INVENTTRANS OUT MERGED'[INVENTTRANS.DATEPHYSICAL]>=_Startdate)

Return

SUMX(_Table;'INVENTTRANS OUT MERGED'[INVENTTRANS.QTY]*-1)

But I want to change the return part. Just for some context, I'm trying to calculate the stock turnover, but for certain items that calculation needs to be different from others. Therefore I already wrote 2 variables to classify those requirements. What I want in the end is: 

When the BOM version. BOMID returns 1 I want to keep the same result as now presented, so INVENTTRANS.QTY *-1 on the filtered _Table. But when the BOM version.BOMID returns 0 (so not blank) I want the INVENTTRANS.QTY to be filtered, because I don't want to include the qty that has "WO" in the REFERENCEID. Therefore I already put in the requirement for identifying "WO", but after that I'm lost. I've tried to do it with an IF statement but because I'm trying to combine 2 calculations into 1, I don't have a necessary True or false outcome. It just needs to be one or the other. 

First the item table in which the BOMid is registered. 

ITEMID	BOMID
dog	"Blank"
cat	B001

Then, the table in which the quantity is registered per referenceid.

ITEMID	REFERENCEID	QTY
dog	WO1	5
dog	WO2	5
dog	IC01	5
dog	IC02	5
dog	SO1	5
dog	SO2	5
cat	WO3	5
cat	WO4	5
cat	IC03	5
cat	IC04	5
cat	SO3	5
cat	SO4	5

And finally, the numbers PowerBI should come up with. 

ITEMID	QTY
dog	30
cat	20

So just to summarize, if the bomid isn't empty I want to include all the quantities, except the ones that contain "WO" in the REFERENCEID. If the bomid is empty, I want to include all the quantities, so also "WO". I didn't elaborate on the maxdate function, because that's already doing what it should do. 

I really appreciate the help!

Gr. Diana

[DataInsights · Accepted solution]

@DLU,

This solution uses the data model below:

Measures:

Sum QTY = SUM ( 'INVENTTRANS OUT MERGED'[QTY] )

Total Trans QTY LTM = 
VAR vTable =
    ADDCOLUMNS (
        'ITEMID BOM',
        "@Qty",
            VAR vBOMID = 'ITEMID BOM'[BOMID]
            RETURN
                IF (
                    vBOMID = "Blank",
                    [Sum QTY],
                    CALCULATE (
                        [Sum QTY],
                        NOT CONTAINSSTRING ( 'INVENTTRANS OUT MERGED'[REFERENCEID], "WO" )
                    )
                )
    )
VAR vResult =
    SUMX ( vTable, [@Qty] )
RETURN
    vResult

Add 'ITEMID BOM'[ITEMID] and the second measure to a visual:

[DLU]

Thank you so much for your help DataInsights, I was really stuck on this for a few weeks. I hope you don't mind me asking, but I always try to figure out how the DAX works to I can use it for future purposes also. Could you explain what the "@qty" function does? I haven't seen it before in other DAX measures...How does PowerBI know it needs to use the qty column that's actually called different? And am I interpreting it correctly that the VAR vBOMID is actually not a seperate VAR but it's inside the VAR vTable? 

[DataInsights]

@DLU,

Glad to hear that works. The "@Qty" is actually a temporary column; you can name it anything you want. The variable vBOMID is a nested variable within the variable vTable. Since ADDCOLUMNS is an iterator, vBOMID enables us to get the BOMID for each iteration, and calculate the measure [Sum QTY] in the appropriate context. Once the table has been iterated, vTable contains all the necessary data, and then SUMX iterates vTable.

Hope this helps!

[DLU]

Thank you so much for your willingness to explain it to me, it makes perfect sense now and I'm sure this will help solve a lot of my problems I ran into!