Shippable Inventory Dax & Matrix

[nmoriello]

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!

[parry2k]

@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)
)

[parry2k]

@nmoriello can you share relationship diagram and what columns you are using on the rows.

[nmoriello]

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.

[parry2k]

@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]

Hi Parry2k,

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