Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Inventory Value

Hello!

 

I am currently working on a report with data from an older version of NAV to display infromation about the inventory but I've hit a snag when it comes to displaying the current inventory value.

 

I got two tables to work with:

Items (Contains item prices, descriptions etc.)

Item Ledger Entry (Contains "Remaining Quantity" which is a series of +1 -1 for each item)

The tables are linked by Item No

 

All i really want to do is multiply the sum of "Remaining Quantity" for each item with the item price but can't figure out how.

 

  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    you need to relate your tables:

     

    and then the measures are easy

     

    RemainingQuantity = SUM( Transactions[Quantity] )

     

    Value =
             SUMX(
                     Items,

                     [RemainingQuantity] * Items[UnitPrice]
              )

     

     

5 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    Anonymous

     

    please post a sample of your data along with expected results

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Sample Data:

      No | Description | UnitPrice
      10  | Adapter |99

       

      Entry No | Item No | Quantity | Remaining Quantity
      1 | 10 | 1 | 1
      2 | 10 | -1 | 0

      3 | 10 | 2 | 2

       

      Goal:

       

      No | Remaining Quantity | Value(Remaining Quanitity * UnitPrice)

      10 | 2 | 198

      • LivioLanzo's avatar
        LivioLanzo
        Solution Sage
        7 years ago

        you need to relate your tables:

         

        and then the measures are easy

         

        RemainingQuantity = SUM( Transactions[Quantity] )

         

        Value =
                 SUMX(
                         Items,

                         [RemainingQuantity] * Items[UnitPrice]
                  )