Filter a measure by different criteria

[khappersett]

I am trying to find average order value for customers who spent more than $500 in a year. I have a measure that will give me an overall average order (simply [2016 sales] / distinctcount(order_id)). I am having a lot of trouble figuring out how to filter this to only include customers who spent more than $500. I have tried creating a measure that returns Y or N to define each customer define as spending more than $500 or not and then filtering by this, but it does not work. I also cannot have this Y/N measure as a column because it will change depending what brand, etc. is selected. This is what I have at the moment:

2016 >$500 Avg Booked $ Per Order = var c = [2016 500+ Y/N] return CALCULATE(DIVIDE([2016 Booked$], DISTINCTCOUNT('Order Detail-Bookings'[ORDER_ID])), FILTER('Order Detail-Bookings', c= "Y"))

[mattbrice]

What is the granularity of the "Order Detail-Booking" table?  does 1 row = 1 order?  or do multiple rows potentially make up 1 order?

I think you posted previously on this , but for Average < $1000?

[khappersett]

One row is one order - I posted a different question about the >1000 case, which was dealing with a distinct count. I have gotten that to work but am now stuck on this.

[mattbrice]

Off the cuff, give this a try:

Measure =
AVERAGEX (
    FILTER (
        SUMMARIZECOLUMNS (
            'Order Detail-Bookings'[CustomerID],
            "Sum Amount", SUM ( 'Order Detail-Bookings'[Order Amount] )
        ),
        [Sum Amount] > 500
    ),
    [Sum Amount]
)

[khappersett · Accepted solution]

2016 >500 Avg Total Spent = AVERAGEX(
     CALCULATETABLE('Account Detail',
     FILTER('Account Detail', [2016 Booked$] > 500)),
[2016 Booked$])

I think this code is working!! Thank you soooo much - I was trying to figure this out for like 8 hours at work yesterday!

[v-yuezhe-msft]

@khappersett,

Glad to hear the issue is solved,  you can mark appropriate reply as answer so that other community members would easily find the solution when they get same issues.

Regards,
Lydia

[khappersett]

I have altered it a bit to this:

2016 >500 Avg Total Spent = AVERAGEX(
     SUMMARIZECOLUMNS('Account Detail'[WHOLE_S_ID],
          FILTER('Account Detail', [2016 Booked$] > 500),
          "sum amount", [2016 Booked$]),
     [sum amount])

Still getting the same error when I try adding it to the table with the rest of my measures, but getting the same number as your code did when adding to a table by itself.

[khappersett]

Hmm - when I add that to my table, I get this error:

If I add it to a new table by itself, I get a number, but it is not correct. This is a helpful way of thinking though that could be useful! I'll have to play around with it some more.