Forum Discussion
need help to divide with 2 condition
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
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.Customer
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
<p>
Hello Nasif ,
i am using simple excel file imported to power bi,if you can support.
thank you.<p>
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