Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Measure - Total order value for orders with item

Hi!

 

I need to summarize the value of all orders containing an item.

I made a simple model as an example

 

 

For example the total order value for ItemA (1) i want to be 1025. Because ItemA is contained i Order 1 and 2 (600 + 425).

I cant get my head around how to write the DAX formula. I need to keep the item filter to get the orderid but at the same time need to get all the orderId's including the ones that the Item relation has filtered.

 

 

Please advice :-)

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    Create a calculated column in 'Orders' table.

    Sales per order =
CALCULATE ( SUM ( Orders[Sales] ), ALLEXCEPT ( Orders, Orders[OrderId] ) )

    Create a measure based on 'Item' table.

    sales per item =
CALCULATE (
    SUM ( Orders[Sales per order] ),
    ALLEXCEPT ( 'Item', 'Item'[ItemName] )
)

    Then, you can add 'Item'[ItemName], 'Item'[Sales] and above measure [sales per item] into table visual.

     

    Best regards,

    Yuliana Gu

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    Create a calculated column in 'Orders' table.

    Sales per order =
CALCULATE ( SUM ( Orders[Sales] ), ALLEXCEPT ( Orders, Orders[OrderId] ) )

    Create a measure based on 'Item' table.

    sales per item =
CALCULATE (
    SUM ( Orders[Sales per order] ),
    ALLEXCEPT ( 'Item', 'Item'[ItemName] )
)

    Then, you can add 'Item'[ItemName], 'Item'[Sales] and above measure [sales per item] into table visual.

     

    Best regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks v-yulgu-msft

       

      This also did the trick:

      SalesTotalOrder = CALCULATE(SUM(Orders[Sales]);ALL(Orders);VALUES(Orders[OrderId]))

       

      I will try your measures out.