Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
wlljhn
Regular Visitor

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-ItemIDStore IDItem IDHistorical sales Qty
A102-435671A10243567112
A102-435672A1024356729
A102-435673A1024356736
A102-435674A1024356742
A110-435674A1104356744
A123-435671A12343567121
A123-435674A1234356749
A145-435672A14543567212
A145-435674A14543567415

 

(2) Transaction data table

Transaction Data table   
StoreID-itemIDStore IDItem IDTransaction DateQty
A102-435672A10243567219-Oct-203
A102-435674A10243567419-Oct-2020
A145-435672A14543567220-Oct-2012
A123-435674A12343567420-Oct-2025
A102-435672A10243567222-Oct-2010
A102-435674A10243567422-Oct-207
A110-435674A11043567422-Oct-209
A145-435672A14543567222-Oct-204

 

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 ID19-Oct-2020-Oct-2021-Oct-2022-Oct-2023-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 ID19-Oct-2020-Oct-2021-Oct-2022-Oct-2023-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

 

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

@wlljhn this code might work.

wdx223_Daniel_0-1603691575770.png

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

View solution in original post

1 REPLY 1
wdx223_Daniel
Super User
Super User

@wlljhn this code might work.

wdx223_Daniel_0-1603691575770.png

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.