Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

SUMX with 2 variables

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

 

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    Anonymous,

     

    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:

     

     

4 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    3 years ago

    Anonymous,

     

    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:

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        DataInsights
        Icon for Super User rankSuper User
        3 years ago

        Anonymous,

         

        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!