Measure - Total order value for orders with item

[Anonymous]

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 🙂

[v-yulgu-msft · Accepted solution]

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]

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.