Calculated Column based off conditions

[JuicyJ35]

My work has a new Ecommerce system and our sales enter our POS in a different format. Normally our online sales come from the store they are purchased and picked up from, but now the purchase has Ecommerce as the store and it adds an extra $0 product to the purchase and it is named as the store. For our reporting this is an issue because it still shows inventory leaving that store, but it doesn't track it as sales for that store. Here is examples of what a few sales look like.

Store         Quantity  Revenue   Product Name    SaleID

Ecommerce    1              $0            Store A            06543

Ecommerce    1              $5           Product B         06543

And here is what a normal sale from our store looks like

Store         Quantity  Revenue   Product Name    SaleID

Store C            1             $4            Product D       REG-100

I'm thinking of creating a calculated column in PowerBI showing if Store = 'Ecommerce', and if Sale ID is equal, and if Revenue = $0, then show Product Name (which would be the Store). But I can't seem to format it right. I know it can be done I'm just a little stuck. If anyone could give me a hand I would appreciate it.

Here is what I want the calculated column to display.

Store         Quantity  Revenue   Product Name    SaleID   Ecom Store

Ecommerce    1              $0            Store A            06543      Store A

Ecommerce    1              $5           Product B         06543      Store A

[JuicyJ35]

Hello, unfortunetly this didn't work for me. 

[Anonymous · Accepted solution]

Hi @JuicyJ35 ,

Please try to create a new column with below dax formula:

Column =
VAR tmp =
    FILTER ( 'Table', [Store] = "Ecommerce" && [Revenue] = 0 )
VAR _a =
    CALCULATE ( MAX ( 'Table'[Store] ), tmp )
VAR _b =
    CALCULATE ( MAX ( 'Table'[SaleID] ), tmp )
VAR _c =
    CALCULATE ( MAX ( 'Table'[Product Name] ), tmp )
VAR cur_store = [Store]
VAR cur_saleid = [SaleID]
RETURN
    IF ( cur_store = "Ecommerce" && cur_saleid = _b, _c )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.