Forum Discussion

JoeKat07's avatar
JoeKat07
Frequent Visitor
6 years ago
Solved

Calculating Inventory with Prepacked items

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

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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.

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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.

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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.