Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
Newbie here. I'm currently struggling to display sum value of sales vs historical data of active item being transacted on respective date. To illustrate the problem, I have these 2 tables.
(1) Historical data table
Historical Data Table | |||
StoreID-ItemID | Store ID | Item ID | Historical sales Qty |
A102-435671 | A102 | 435671 | 12 |
A102-435672 | A102 | 435672 | 9 |
A102-435673 | A102 | 435673 | 6 |
A102-435674 | A102 | 435674 | 2 |
A110-435674 | A110 | 435674 | 4 |
A123-435671 | A123 | 435671 | 21 |
A123-435674 | A123 | 435674 | 9 |
A145-435672 | A145 | 435672 | 12 |
A145-435674 | A145 | 435674 | 15 |
(2) Transaction data table
Transaction Data table | ||||
StoreID-itemID | Store ID | Item ID | Transaction Date | Qty |
A102-435672 | A102 | 435672 | 19-Oct-20 | 3 |
A102-435674 | A102 | 435674 | 19-Oct-20 | 20 |
A145-435672 | A145 | 435672 | 20-Oct-20 | 12 |
A123-435674 | A123 | 435674 | 20-Oct-20 | 25 |
A102-435672 | A102 | 435672 | 22-Oct-20 | 10 |
A102-435674 | A102 | 435674 | 22-Oct-20 | 7 |
A110-435674 | A110 | 435674 | 22-Oct-20 | 9 |
A145-435672 | A145 | 435672 | 22-Oct-20 | 4 |
These tables relationship is 1 to Many with cross filter direction set to Both (didn't know why but default single setting didn't work).
What I want to display on pbi is illustrated as follow:
∑Qty / Historical Sales Qty | |||||
Item ID | 19-Oct-20 | 20-Oct-20 | 21-Oct-20 | 22-Oct-20 | 23-Oct-20 |
435671 | |||||
435672 | =3/9 | =12/12 | =(10+4)/(9+12) | ||
435673 | |||||
435674 | =20/2 | =25/9 | =(7+9)/(2+4) |
However, I keep on getting this result
∑Qty / Historical Sales Qty | |||||
Item ID | 19-Oct-20 | 20-Oct-20 | 21-Oct-20 | 22-Oct-20 | 23-Oct-20 |
435671 | |||||
435672 | =3/(9+12) | =12/(9+12) | =(10+4)/(9+12) | ||
435673 | |||||
435674 | =20/(2+4+9+15) | =25/(2+4+9+15) | =(7+9)/(2+4+9+15) |
Any help would be greatly appreciated..
Cheers
Will
Solved! Go to Solution.
@wlljhn this code might work.
=DIVIDE(SUM(TransactionData[Qty]),CALCULATE(SUM(HistoricalData[Historical sales Qty]),TransactionData))
@wlljhn this code might work.
=DIVIDE(SUM(TransactionData[Qty]),CALCULATE(SUM(HistoricalData[Historical sales Qty]),TransactionData))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |