Get totalt order revenue for orders containing a specific product

[magnus_b]

Hi!

I have tried searching for solutions, but couldn't find one that worked for my specifc use case.

I have a table like this:

orderid	orderlineid	productname	amount
9871	1	Product A	92
9871	2	Product X	25
1536	1	Product B	150
1536	2	Product A	97
7152	1	Product X	82
7152	2	Product X	51
6128	1	Product A	41
6128	2	Product B	25

I then want to create the following measures:

1. Total order revenue for all orders containing Product X - expected result is 92+25+82+51 = 250
2. Number of orders containing Product X - expected result is 2

3. Total order revenue for all orders except orders containing Product X - expected result is 150+97+41+25 = 313
4. Number of orders except orders containing Product X - expected result is 2

I appreciate any help!

[Anonymous · Accepted solution]

[Total] = SUM( T[Amount] )

[# Orders] = DISTINCTCOUNT( T[Order ID] )

// 1
[Total X] =
var OrdersWithX =
    calculatetable(
        distinct( T[Order ID] ),
        keepfilters (
            T[Product] = "Product X"
        )
    )
return
calculate(
    [Total],
    OrdersWithX
)

// 2
[# Orders X] =
CALCULATE(
    [# Orders],
    KEEPFILTERS(
        T[Product Name] = "Product X"
    )    
)

// 3
[Total ~X] = [Total] - [Total X]

// 4
[# Orders ~X] = [# Orders] - [# Orders X]

[magnus_b]

That worked like a charm! Thanks!