Forum Discussion
Measure Sum based on multiple conditions
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")
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
2 Replies
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
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])
