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