Calculating Inventory with Prepacked items

[JoeKat07]

Hello,

My company sells its items in both prepackaged units and by individual pounds. 

I am trying to do a calculation to find the real inventory in our warehouse (Prepack suffix of 50 is stored in our Inventory table as an each so 1) and its throwing me for a loop. I used the below method to get the right sum for the individual items broken out in a table, however the total comes out to be the sum of each UoM * quantity. The join goes Prepack table <- Item table -> Inventory table.

My calculation is as follows:  _Real QTY = if(ISBLANK(sum(DimPrepackUOM[WeightLBS])), sum(DimInventory[ActualQty]), sum(DimInventory[ActualQty]) * SUM(DimPrepackUOM[WeightLBS]))

Thanks

[v-lid-msft]

Hi @JoeKat07 ,

How about the result after you follow the suggestions mentioned in my original post?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @JoeKat07 ,

We can try to use the following measuer to meet your requirement.

_Real QTY = 
IF (
    ISINSCOPE ( DimInventory[IPC] ),
    IF (
        ISBLANK ( SUM ( DimPrepackUOM[WeightLBS] ) ),
        SUM ( DimInventory[ActualQty] ),
        SUM ( DimInventory[ActualQty] ) * SUM ( DimPrepackUOM[WeightLBS] )
    ),
    SUMX (
        SUMMARIZECOLUMNS (
            'DimInventory'[IPC],
            "qty", IF (
                ISBLANK ( SUM ( DimPrepackUOM[WeightLBS] ) ),
                SUM ( DimInventory[ActualQty] ),
                SUM ( DimInventory[ActualQty] ) * SUM ( DimPrepackUOM[WeightLBS] )
            )
        ),
        [qty]
    )
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.