Help with sumif

wlljhn — Mon, 26 Oct 2020 05:16:38 GMT

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

Re: Help with sumif

wdx223_Daniel — Mon, 26 Oct 2020 05:53:18 GMT

wlljhn this code might work.

=DIVIDE(SUM(TransactionData[Qty]),CALCULATE(SUM(HistoricalData[Historical sales Qty]),TransactionData))

topic Help with sumif in DAX Commands and Tips

Help with sumif

Re: Help with sumif