Forum Discussion
Calculation
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 |
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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-8e3eccb41bdaPower BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
