Forum Discussion

danzen's avatar
danzen
Frequent Visitor
1 year ago
Solved

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

Need Help
Show and Tell
Tutorial Requests
  • Nasif_Azam's avatar
    Nasif_Azam
    1 year ago

    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

9 Replies

    • danzen's avatar
      danzen
      Frequent Visitor
      1 year ago

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

       

      • SundarRaj's avatar
        SundarRaj
        Super User
        1 year ago

        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

  • Nasif_Azam's avatar
    Nasif_Azam
    Super User
    1 year ago

    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's avatar
      danzen
      Frequent Visitor
      1 year ago

      <p>

      Hello Nasif , 

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

      thank you.<p>

      • Nasif_Azam's avatar
        Nasif_Azam
        Super User
        1 year ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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