Calculating the sum of the value of an order

[Anonymous]

Hello everyone,

I need to calculate the sum of the values of the items in a Sales Order.

My report works with three tables: 

• Sales Order table: contains all the sales orders, each identified by a unique code, and all the items inside the SO. If a SO contains more than one item, more rows with the same SO are displayed.
  
  

  SO code	Item Code
  SO1	0002
  SO2	0002
  SO2	0001
  SO3	0004
  SO4	0005
  SO5	0003
  SO5	0001
  SO5	0002

• Item Fulfillment table: contains all the fulfillments of sales orders. Each IF is identified by a unique code. A SO can be fulfilled with more than one IF, in which case, more rows  with the same IF are displayed.
  
  

  IF code	SO code
  IF1	SO1
  IF2	SO1
  IF3	SO1
  IF4	SO3
  IF5	SO5
  IF6	SO2
  IF7	SO2

• Item List: contains all the items and their cost.
  
  

  Item Code	Last Year Cost
  0001	4,64 €
  0002	4,76 €
  0003	2,54 €
  0004	4,48 €
  0005	5,22 €

I built a view based on the IF table. It displays the Sales Orders in the IF table and, when drilling-down to the second level, all the IFs related to that SO. I want to display on another column of the same view the sum of the costs of the items contained in the SO in each row.

Thank you very much for your help!  

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use the following measure formula on your ‘IF’ table visuals to show the correspond sales:

formual =
VAR ItemCodeList =
    CALCULATETABLE (
        VALUES ( 'Sales Order'[Item Code] ),
        FILTER (
            ALLSELECTED ( 'Sales Order' ),
            [SO Code] IN VALUES ( 'Item Fulfillment'[SO Code] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Item List'[Last Year Cost] ),
        FILTER ( ALLSELECTED ( 'Item List' ), [Item Code] IN ItemCodeList )
    )

Regards,

Xiaoxin Sheng