Measure Sum based on multiple conditions

[fbron]

Based on the data below id like to create a measure to calulate the sum of the coumn 'Quantity' with multiple conditions. 

Data Example

For example, I'd like to know the total of column quantity for the orders with ordertype "0" and  orderstatus "on hold" and "open":

I already tried the following:

Total ongoing Ordertype 0 = CALCULATE(SUM(Order Table[Quantity]),

      FILTER(Order Table, Order Table[Ordertype]= "0"),

      FILTER(Order Table, Order Table[Order Status]= "Open"),

      FILTER(Order Table, Order Table[Order Status]= "On Hold")

)

Expected value would be 4 but somehow this doesnt show up. 

What am I doing wrong here? Any suggestions?

PS:

Ordertype (=column type "Whole Number")

Order status (=column type "Text")

Quantity (=column type "Decimal Number")

[CNENFRNL]

Hi, @fbron , why bother to hardcode those filtering conditions in a measure? Slicers with a simple measure do the trick with ease and with more flexibilities. Dax is born for such a scenario. An Excel file is attached for your reference.

Total Qty = SUM(Order Table[Quantity])

[AlB · Accepted solution]

Hi @fbron 

If Ordertype (=column type "Whole Number") you ned to compare to a number, not to text

You are doing a logical AND for Open and On hold for Order status. That would mean you want it to be Open and On hold at the same time. You need an OR instead:

Total ongoing Ordertype 0 =
CALCULATE (
    SUM ( 'Order Table'[Quantity] ),
    'Order Table'[Ordertype] = 0,
    'Order Table'[Order Status] = "Open" || 'Order Table'[Order Status] = "On Hold"
)

or alternatively

Total ongoing Ordertype 0 =
CALCULATE (
    SUM ( 'Order Table'[Quantity] ),
    'Order Table'[Ordertype] = 0,
    'Order Table'[Order Status] IN  {"Open", "On Hold"}
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers