Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
Hi @Ak_26696 ,
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.
Hi @Ak_26696 ,
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.
@Ak_26696 , 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-soluti...
Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |