IF SUM Statement DAX

[mbbui0721]

Hello,

I'm fairly new to DAX. I'm tying to compare the TYPE of just Work Order Warranty and Warranty Transaction based on the total QTY of the item. If the Sum of QTY of Work Order Warranty is greater than Warranty Transactions then out "Fullfilled" else "Pending" as a new column. 

ORG	Date	Item	TRX Value	QTY	TYPE
1	1/1/2021	A051787410541	5	8	Work Order Annual/Modular/Parts Rebuild
2	1/2/2021	A052800208159	7	5	Work Order Repair
1	1/3/2021	A0560326372	5.5	8	Work Order Warranty
2	1/4/2021	A0560326372	6.7	5	Warranty Transaction
1	1/5/2021	A051787410541	8.9	7	Work Order Annual/Modular/Parts Rebuild
2	1/6/2021	A052800208159	8.87	8	Work Order Annual/Modular/Parts Rebuild
1	1/7/2021	A0560326372	9.62	5	Work Order Repair
1	1/8/2021	A0560326372	10.37	8	Work Order Warranty

[DataInsights · Accepted solution]

@mbbui0721,

Try this calculated column. I'm not sure if you want "Pending" for every row that is not "Fulfilled", or for every row that is not "Fulfilled" and TYPE = "Work Order Warranty" or "Warranty Transaction". The DAX can be easily adjusted to accommodate either scenario.

Status = 
VAR vWorkOrderWarranty =
    CALCULATE (
        SUM ( Table1[QTY] ),
        ALLEXCEPT ( Table1, Table1[Item] ),
        Table1[TYPE] = "Work Order Warranty"
    )
VAR vWarrantyTransaction =
    CALCULATE (
        SUM ( Table1[QTY] ),
        ALLEXCEPT ( Table1, Table1[Item] ),
        Table1[TYPE] = "Warranty Transaction"
    )
VAR vResult =
    SWITCH (
        TRUE (),
        Table1[TYPE]
            IN { "Work Order Warranty", "Warranty Transaction" }
            && vWorkOrderWarranty > vWarrantyTransaction, "Fulfilled",
        Table1[TYPE]
            IN { "Work Order Warranty", "Warranty Transaction" }
            && vWorkOrderWarranty <= vWarrantyTransaction, "Pending"
    )
RETURN
    vResult