Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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))
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |