Forum Discussion
Calculated Column based off conditions
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.
Ecommerce 1 $0 Store A 06543
Ecommerce 1 $5 Product B 06543
And here is what a normal sale from our store looks like
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.
Ecommerce 1 $0 Store A 06543 Store A
Ecommerce 1 $5 Product B 06543 Store A
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.
2 Replies
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.Hello, unfortunetly this didn't work for me.
