Forum Discussion
Solved
Help with sumif
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
wlljhn this code might work.
=DIVIDE(SUM(TransactionData[Qty]),CALCULATE(SUM(HistoricalData[Historical sales Qty]),TransactionData))
1 Reply
- wdx223_Daniel
Community Champion
wlljhn this code might work.
=DIVIDE(SUM(TransactionData[Qty]),CALCULATE(SUM(HistoricalData[Historical sales Qty]),TransactionData))