Forum Discussion

nmoriello's avatar
nmoriello
Frequent Visitor
1 year ago

Shippable Inventory Dax & Matrix

Hello,

I have a Power BI Report that is using 4 different tables (Order Backlog, Inventory, Incoming Shipments & Production) I am trying to leverage all 4 tables to see what amount of inventory to fulfil my "Order Backlog" orders. I currently have a Dax measure that looks like this: 

 

Shippable Qty Total =
VAR BacklogQty = [YTD Qty Total]
VAR OnHandQty = SUM('Total On Hand Qty'[OnHandQty])
VAR IncomingQty = SUM('Total CZUB Shipments'[Qty])
VAR ProductionQty = SUM('Total Unit Production'[Calculated_Qty])
VAR TotalAvailableQty = OnHandQty + IncomingQty + ProductionQty
RETURN
MIN(BacklogQty, TotalAvailableQty)
 
It seems to be doing the correct calculations from a bottom row level basis. But once I put it into a matrix and stack some categories on top of the Part Numbers in each row such as Part Cateogry & Company then the calculation doesnt add up. For example in the screenshot below, the numbers under that 21,004 do not add up correctly. It should be displaying 20,356. I am not sure where the disconnect is coming from, it seems to be doing the correct calculation at the row level, but one the top level of the matrix is loses it. Any help would be great!
 

 


 


 

Need Help

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    nmoriello try this:

     

    Shippable Qty Total =
SUMX ( 
   VALUES ( productTable[Product] ),
   VAR BacklogQty = [YTD Qty Total]
   VAR OnHandQty = CALCULATE(SUM('Total On Hand Qty'[OnHandQty]))
   VAR IncomingQty = CALCULATE(SUM('Total CZUB Shipments'[Qty]))
   VAR ProductionQty = CALCULATE(SUM('Total Unit Production'[Calculated_Qty]))
   VAR TotalAvailableQty = OnHandQty + IncomingQty + ProductionQty
   RETURN
   MIN(BacklogQty, TotalAvailableQty)
)
    • nmoriello's avatar
      nmoriello
      Frequent Visitor
      1 year ago

      Hi Parry2k,

       

      That unfortunetly didnt work, it doesnt look like its even calculating the row level correctly with the SUMX and VALUES.

       

       

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    nmoriello try this, if not It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.

     

     

    
Shippable Qty Total =
SUMX ( 
   ProductTable,
   VAR BacklogQty = [YTD Qty Total]
   VAR OnHandQty = CALCULATE(SUM('Total On Hand Qty'[OnHandQty]))
   VAR IncomingQty = CALCULATE(SUM('Total CZUB Shipments'[Qty]))
   VAR ProductionQty = CALCULATE(SUM('Total Unit Production'[Calculated_Qty]))
   VAR TotalAvailableQty = OnHandQty + IncomingQty + ProductionQty
   RETURN
   MIN(BacklogQty, TotalAvailableQty)
)

     

    • nmoriello's avatar
      nmoriello
      Frequent Visitor
      1 year ago

      Since I have multiple Companies on each table I created a unique link (PartNum Link). That each table in the diagram below is linking to the Part Table. That allows me to geat to each part from the tables.

       

      As for the Matrix I am using the PartNumber as the bottom value, then above that I am using a PartCategory field that lives on that same Part Table.