Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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-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
Solved! Go to Solution.
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
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.
So what is the expected output?
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |