Finding an average order value where order contains X

[MCornish]

Hi All

Got a brainteaser here. 

I have a report that looks at a subset of products. From this I can get all the usual stuff (value, qtys etc) however what I want to find is the average order value of orders that contain my subset but not limited to .

Not a very good explanation so here is what Ive got so far

The total order value for order 123456 is correct because all it contains is Product X. However 258369 is wrong because its only looking at line 1, it should be £10 like below.

And because 147258 doesnt contain Product X its excluded. This makes the AOV £12.50

Hope you can help and I've explained it thoroughly enough

Cheers

[v-eachen-msft · Accepted solution]

Hi @MCornish ,

Hi @MCornish ,

You could create a column to set a flag.

Flag =
VAR a =
    ADDCOLUMNS (
        'Product',
        "c", IF ( CONTAINSSTRING ( "X", 'Product'[Product] ), 0, 1 )
    )
RETURN
    PRODUCTX (
        FILTER ( a, 'Product'[OrderNumber] = EARLIER ( 'Product'[OrderNumber] ) ),
        [c]
    )

Then create a measure to calculate average.

_AOV =
CALCULATE (
    SUMX ( FILTER ( 'Product', 'Product'[Flag] = 0 ), 'Product'[Value] )
        / CALCULATE (
            DISTINCTCOUNT ( 'Product'[OrderNumber] ),
            FILTER ( 'Product', 'Product'[Flag] = 0 )
        ),
    ALLSELECTED ( 'Product' )
)

Here is the result.

[MCornish]

Think I may have got it, can anybody validate that i'm not doing something weird

AOV = 

VAR Orders = SUMMARIZECOLUMNS('Product Entries'[Order Number], "SalesValue", [Total Sales Value])
VAR AllOrders = INTERSECT( CALCULATETABLE( SUMMARIZECOLUMNS( 'Product Entries'[Order Number], "SalesValue", [Total Sales Value]), REMOVEFILTERS( 'Product Brand'[Brand Name] ) ), Orders )

RETURN

AVERAGEX( AllOrders, [SalesValue] )