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

Anonymous — Sat, 18 Apr 2020 19:44:56 GMT

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-A	B	C	D
Month	Item_No	Planned Purchase Qty [Measure PBI]	Container Plan
Jan	Item1	0	0
Jan	Item2	2	0.1
Jan	Item3	67	0.2
Jan	Item4	508	0.7
Jan	Item5	12572	31.5
Jan	Item6	776	2.3
Jan	Item8	2135	8.6
Jan	Item9	345	0.7
Jan	Item10	125	0.3
Feb	Item1	1	0.1
Feb	Item2	1	0.1
Feb	Item3	163	0.3
Feb	Item4	157	0.2
Feb	Item5	5814	14.6
Feb	Item6	1845	5.3
Feb	Item8	1568	6.3
Feb	Item9	256	0.6
Feb	Item10	539	0.9
Mar	Item1	0	0
Mar	Item3	170	0.3
Mar	Item4	371	0.5
Mar	Item5	8190	20.5
Mar	Item6	1503	4.3
Mar	Item8	156	0.7
Mar	Item9	406	0.9
Mar	Item10	12	0.1
Apr	Item4	1400	1.8
Apr	Item5	8686	21.8
Apr	Item6	1661	4.8
Apr	Item7	182	0.3
May	Item4	992	1.3
May	Item5	7222	18.1
May	Item6	1751	5.1
May	Item7	1701	2.2
Jun	Item4	811	1.1
Jun	Item5	10478	26.2
Jun	Item6	1786	5.2
Jun	Item7	989	1.3
Jun	Item9	203	0.5
Jun	Item10	36	0.1
Jul	Item1	1	0.1
Jul	Item4	393	0.5
Jul	Item5	9236	23.1
Jul	Item6	1541	4.5
Jul	Item7	1231	1.6
Jul	Item9	875	1.8
Jul	Item10	113	0.2
Aug	Item4	940	1.2
Aug	Item5	10202	25.6
Aug	Item6	2140	6.2
Aug	Item7	345	0.5
Aug	Item10	61	0.2
Sep	Item4	2026	2.6
Sep	Item5	13138	32.9
Sep	Item6	1393	4
Sep	Item7	399	0.5
Sep	Item9	1248	2.5
Sep	Item10	126	0.3
Oct	Item1	0	0
Oct	Item4	1761	2.3
Oct	Item5	9488	23.8
Oct	Item6	404	1.2
Oct	Item7	347	0.5
Oct	Item9	669	1.4
Oct	Item10	473	0.8
Nov	Item1	0	0
Nov	Item4	1362	1.8
Nov	Item5	10598	26.5
Nov	Item6	1887	5.4
Nov	Item7	1165	1.5
Nov	Item8	3548	14.2
Nov	Item9	4408	8.9
Nov	Item10	293	0.5
Dec	Item4	2	0.1
Dec	Item5	11648	29.2
Dec	Item6	188	0.6
Dec	Item7	143	0.2
Dec	Item8	3066	12.3
Dec	Item9	3954	8
Dec	Item10	268	0.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-A	B
Item_No	MOQ
Item1	250
Item2	500
Item3	600
Item4	800
Item5	400
Item6	350
Item7	800
Item8	250
Item9	500
Item10	600

Please help.

thanks

Musfeq Saleheen

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

Greg_Deckler — Sat, 18 Apr 2020 22:18:12 GMT

So what is the expected output?

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

Anonymous — Sun, 19 Apr 2020 02:45:06 GMT

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

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

Anonymous — Sun, 19 Apr 2020 06:50:19 GMT

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

topic Re: DAX querry to divide output of measure with a column data of a table in DAX Commands and Tips

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

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

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

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