Create a filter with amounts per Customer coming from several Opportunities - it can't be a measure

[AliceW]

Hi guys,

This should be simple, but I just can't figure out how to do it, so help :0)

What I have:

I have a table with Customers and Opportunities. One Customer can have one or several Opportunities.

What I need:

To calculate a column (so I can use as a page filter) with the Total Amount per Customer. Based on that, I can create various categories (see 'Customer Total' column below).

Customer	Opp #	Amount	Customer Total
AAA	1	1000	Between 0 and 3000
AAA	2	2000	Between 0 and 3000
AAA	3	3000	Between 0 and 3000
BBB	4	4000	Between 3001 and 5000
BBB	5	5000	Between 3001 and 5000

If I just create a measure CustomerTotal = sum(Amount) I won't have a filter... 😞

Big thanks,

Alice

[edhans · Accepted solution]

You can create a calculated column that has customer totals using this:

Customer Total = 
VAR CurrentCustomer = Sales[Customer]
RETURN
    CALCULATE(
        SUM(Sales[Amount]),
        FILTER(
            Sales,
            Sales[Customer] = CurrentCustomer
        )
    )

You can then create another column to use as your filter amount using SWITCH for your ranges:

Customer Range = 
SWITCH(
    TRUE(),
    Sales[Customer Total] <  3000, "Between 0 and 3000",
    AND(Sales[Customer Total] > 3000,Sales[Customer Total] <= 5000), "Between 3001 and 5000",
    "Over 5000"
    )

[AliceW]

Thank you so much!!