Conditional sum of rows regrouped by type-CodePudding

I have 3 columns:

BL = represents the number of the order, there is multiple rows by BL because orders have different products

ENTMVT: represent the sales ("610" means command has been sent) SORMVT: represent the return ("700" means order has been billed, 610, order has been returned)

I want to keep only BL which ALLS arrows are "610" (in fact I want to see how many commands are totally return) to visualize in Power Bi but I didn't find a conditional sum function in power bi to do so (with the conditional column tool)

can you help me with this matter?

I try to add a calculated column as said, but M seems not to offer SWITCH function:

CodePudding user response：

It's actually quite simple, you have two options:

Create a calculated column where you define the status

Status = SWITCH(TRUE(),
   Table[ENTMVT] = 610 && Table[SORMVT] = 610, "Returned",
   Table[ENTMVT] = 610 && Table[SORMVT] = 700, "Billed",
   "Default Status")

And now you can use this column in any chart and apply filters on it, etc..

In your measure pass it as a filter

Count BL Returned = CALCULATE(COUNT(Table[BL]), Table[ENTMVT] = 610 && Table[SORMVT] = 610)
    
Count BL Billed = CALCULATE(COUNT(Table[BL]), Table[ENTMVT] = 610 && Table[SORMVT] = 700)