Forum Discussion
IF SUM Statement DAX
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 |
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
1 Reply
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