Forum Discussion
need help to divide with 2 condition
i need to a formula that calculate the number of carton of each document number of carton = stock / qty in the box of each document note : each document have a specific quantity in the box , meaning it can be that the same material have different document and these document have specific quantities in the box like in the example Table 1 Material stock Document customer A0001 50 10004145 customer 1 A0001 50 10004146 customer 1 B0001 150 10004147 customer 2 F0001 50 10004148 customer 2 table 2 Document customer qty in the box 10004145 customer 1 40 10004146 customer 1 60 10004147 customer 2 50 10004148 customer 2 70
Hey danzen ,
Thanks for the clarification. Since you're using a simple Excel file imported into Power BI, here’s how you can calculate the number of cartons per document using Power Query.
Load both tables into Power BI
Click Home > Get Data > Excel and load both sheets (Table 1 and Table 2).
Open Power Query
Go to Home > Transform Data.
Rename the tables:
StockTable (for Table 1)
BoxQtyTable (for Table 2)
Merge the tables
In StockTable, go to Home > Merge Queries > Merge Queries as New
Select Document and Customer from both tables
Join kind: Inner Join
Expand the merged table
After merging, click the small expand icon next to the new column
Select only the [qty in the box] column to expand
Add a Custom Column for Cartons:
Go to Add Column > Custom Column
Name it Cartons
Formula:
[Stock] / [qty in the box]
Close & Apply 😊
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
9 Replies
Hi SundarRaj , yes i want that specific result , could you please support how to write that formula, thank you very much.
- Nasif_Azam
Super User
Hey danzen ,
To calculate the number of cartons for each document, you need to:
Join Table 1 and Table 2 based on the Document and Customer.
Divide the Stock from Table 1 by the Qty in the box from Table 2.
If you are using Power Query (Excel or Power BI):
Load both tables into Power Query.
Merge the tables on Document and Customer.
Add a Custom Column:
Cartons := [Stock] / [qty in the box]
If you are using SQL:
Assuming Table1 is StockTable and Table2 is BoxQtyTable:
SELECT s.Material, s.Stock, s.Document, s.Customer, b.[qty in the box], CAST(s.Stock AS FLOAT) / b.[qty in the box] AS Cartons FROM StockTable s JOIN BoxQtyTable b ON s.Document = b.Document AND s.Customer = b.CustomerIf you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam<p>
Hello Nasif ,
i am using simple excel file imported to power bi,if you can support.
thank you.<p>
- Nasif_Azam
Hey danzen ,
Thanks for the clarification. Since you're using a simple Excel file imported into Power BI, here’s how you can calculate the number of cartons per document using Power Query.
Load both tables into Power BI
Click Home > Get Data > Excel and load both sheets (Table 1 and Table 2).
Open Power Query
Go to Home > Transform Data.
Rename the tables:
StockTable (for Table 1)
BoxQtyTable (for Table 2)
Merge the tables
In StockTable, go to Home > Merge Queries > Merge Queries as New
Select Document and Customer from both tables
Join kind: Inner Join
Expand the merged table
After merging, click the small expand icon next to the new column
Select only the [qty in the box] column to expand
Add a Custom Column for Cartons:
Go to Add Column > Custom Column
Name it Cartons
Formula:
[Stock] / [qty in the box]
Close & Apply 😊
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
Hi danzen,
Thanks for reaching out to the Microsoft fabric community forum.
It looks like you want to frame a formula where you can use two conditions simultaneously on your data. As SundarRaj and Nasif_Azam both have responded to your query, kindly go throught their responses and check if your issue can be resolved.
I would also take a moment to thank SundarRaj and Nasif_Azam, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support Team
