Calculation

[Anonymous]

I have two table named Sales and Shops.

I want to do is if Shop Name from Sales table and Shop from Shops table data are same, return Qty column from Sales Table.

If not same, Divide Qty with Unit column from Shops.

I can't find the best solution for it. How can I do that?

Sales
Shop Name	Qty
AAA	30
BBB	44
CCC	20
DDD	16

Shops
Shop	Unit
JJJ	3
AAA	5
GGG	2
CCC	3
KKK	4

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

Data model:

I think you can try this code to create a calculated column in shops table.

Column = 
VAR _QTY = CALCULATE(SUM(Sales[Qty]))
VAR _ALL = SUM(Sales[Qty])
RETURN
IF(_QTY = BLANK(), DIVIDE(_ALL,Shops[Unit]),_QTY)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create a common Dimension Shop from both the tables (if have date, create date too) and use that in conditional formatting

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solution-8e3eccb41bda

Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw