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
Anonymous
Not applicable

DAX querry to divide output of measure with a column data of a table

Hi 

I have problem which looks simple but i am not able to complete in Power Bi. In excel it works within a sec :). 
Here is the problem : 
I have a measure which calculates Planned purchase Quantities by months and itemwise for planning purchase operation. I was happy and delivered. But now users wants to add one more column in visualization where it displays the number of containers requires to load those qtys. I have now another table given by user with MOQ ( Minimum Order Qty) to fill container. So basically now i have to divide the measure data with MOQ. But i am not able to do it. Please help.

 

Current Visual output with additional column (Container Plan) which i have added in excel and i need to achieve that in PowerBI ( With the formula which i have used in excel.

 

Col-ABCD
MonthItem_NoPlanned Purchase Qty [Measure PBI]Container Plan
JanItem100
JanItem220.1
JanItem3670.2
JanItem45080.7
JanItem51257231.5
JanItem67762.3
JanItem821358.6
JanItem93450.7
JanItem101250.3
FebItem110.1
FebItem210.1
FebItem31630.3
FebItem41570.2
FebItem5581414.6
FebItem618455.3
FebItem815686.3
FebItem92560.6
FebItem105390.9
MarItem100
MarItem31700.3
MarItem43710.5
MarItem5819020.5
MarItem615034.3
MarItem81560.7
MarItem94060.9
MarItem10120.1
AprItem414001.8
AprItem5868621.8
AprItem616614.8
AprItem71820.3
MayItem49921.3
MayItem5722218.1
MayItem617515.1
MayItem717012.2
JunItem48111.1
JunItem51047826.2
JunItem617865.2
JunItem79891.3
JunItem92030.5
JunItem10360.1
JulItem110.1
JulItem43930.5
JulItem5923623.1
JulItem615414.5
JulItem712311.6
JulItem98751.8
JulItem101130.2
AugItem49401.2
AugItem51020225.6
AugItem621406.2
AugItem73450.5
AugItem10610.2
SepItem420262.6
SepItem51313832.9
SepItem613934
SepItem73990.5
SepItem912482.5
SepItem101260.3
OctItem100
OctItem417612.3
OctItem5948823.8
OctItem64041.2
OctItem73470.5
OctItem96691.4
OctItem104730.8
NovItem100
NovItem413621.8
NovItem51059826.5
NovItem618875.4
NovItem711651.5
NovItem8354814.2
NovItem944088.9
NovItem102930.5
DecItem420.1
DecItem51164829.2
DecItem61880.6
DecItem71430.2
DecItem8306612.3
DecItem939548
DecItem102680.5

 

Formula used in excel

ContainerPLan=ROUNDUP(C2/INDEX(MOQ!$B$2:$B$11,MATCH(containerplan!B2,MOQ!$A$2:$A$11,0)),1)

 


MOQ table

Col-AB
Item_NoMOQ
Item1250
Item2500
Item3600
Item4800
Item5400
Item6350
Item7800
Item8250
Item9500
Item10600

 

Please help.

 

thanks

Musfeq Saleheen

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi

 

The expected output is the "Container Plan" colum. It's basically the [planned purchase qty] / [ the corresponding MOQ of item from another table]

 

thanks

Musfeq Saleheen

View solution in original post

Anonymous
Not applicable

Hi All

 

There was a problem in the relationship between my MOQ[ItemNo] and ItemLedger[ItemNo]. By default it took single cross filter direction. I changed to Both now my Measure works perfect. 

 

ContainerPlan = DIVIDE([Planned Purchase Qty],CALCULATE(SUM(MoQList[MOQ])))
 
thanks all
Musfeq Saleheen
 

View solution in original post

3 REPLIES 3
Greg_Deckler
Community Champion
Community Champion

So what is the expected output?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi

 

The expected output is the "Container Plan" colum. It's basically the [planned purchase qty] / [ the corresponding MOQ of item from another table]

 

thanks

Musfeq Saleheen

Anonymous
Not applicable

Hi All

 

There was a problem in the relationship between my MOQ[ItemNo] and ItemLedger[ItemNo]. By default it took single cross filter direction. I changed to Both now my Measure works perfect. 

 

ContainerPlan = DIVIDE([Planned Purchase Qty],CALCULATE(SUM(MoQList[MOQ])))
 
thanks all
Musfeq Saleheen
 

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.