need help to divide with 2 condition

[danzen]

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

[v-mdharahman]

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

[danzen]

https://docs.google.com/spreadsheets/d/1G7NkVFRfVIy6i0n1AQC_CbKxYyRiBq0wKVHWU8Efk0A/edit?gid=0#gid=0

[Nasif_Azam]

Hey @danzen ,

To calculate the number of cartons for each document, you need to:

1. Join Table 1 and Table 2 based on the Document and Customer.

2. 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):

1. Load both tables into Power Query.

2. Merge the tables on Document and Customer.

3. 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

[danzen]

<p>

Hello Nasif , 

i am using simple excel file imported to power bi,if you can support.

thank you.<p>

[Nasif_Azam · Accepted solution]

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.

1. Load both tables into Power BI

   • Click Home > Get Data > Excel and load both sheets (Table 1 and Table 2).

2. Open Power Query

   • Go to Home > Transform Data.

3. Rename the tables:

   • StockTable (for Table 1)

   • BoxQtyTable (for Table 2)

4. 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

5. 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

6. Add a Custom Column for Cartons:

   • Go to Add Column > Custom Column

   • Name it Cartons

   • Formula:

     [Stock] / [qty in the box]

7. 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

[SundarRaj]

Hi @danzen , is this the solution / output you were looking for? I'll attach the image below for your reference. Let me know I've understood it correctly. I'll attach the file link in case this is what you were looking for. Let me know if there are some changes. Thanks

File Link:
https://docs.google.com/spreadsheets/d/1CNCTkQtPTbbStYOK4e9p_Dk2D6ezXdaC/edit?usp=sharing&ouid=10475...

[danzen]

Hi SundarRaj , yes i want that specific result , could you please support how to write that formula, thank you very much.

[SundarRaj]

Sure @danzen, did you happen to check out the file? You'll see the entire code and process. Let me know if you need a step by step explaination of it. Thanks

[danzen]

hello Sundar, 

your solution applies but is it is very compressed and shortened for beginner like myself.

i thank you for your support.