cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## 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
1 ACCEPTED SOLUTION
Super User

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``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

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``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors