Comparing to other customers in region

[djh]

I have a list of customers, and their sales. What i am trying to do, is compare sales compared to other customers in the same region.

What i can't work out, is how to refer to the region of the customer currently selected in the slicer.

Customer A is in the slicer, so its showing all the sales info for customer A. They are in region 1.

How do i write a measure to compare Customer A sales to the rest of region 1?

I can do = CALCULATE(Sales, FILTER(Customer List, Customer List[Region])="region 1") to basically get what i want, but i need it to be dynamic so that when i change my slicer, it will know the region of the new customer. somethink like a me. operator?

Suggestions?

[OwenAuger · Accepted solution]

@djh

The structure of a measure that should do the trick is:

=
CALCULATE (
    Sales,
    ALL ( 'Customer List' ),
    VALUES ( 'Customer List'[Region] )
)

VALUES ( 'Customer List'[Region] ) returns the Regions of the current selection in the 'Customer List' table.

ALL ( 'Customer List' ) clears any other filters on this table (such as the selected Customer) so you are left with the sales for the entire Region.

Oh, I'm assuming that 'Customer List' is a separate dimension table, related to your main Sales table. If not, we would need to be more careful about which filters we clear with ALL.

Regards,

Owen

[djh]

Perfect, thanks! it did exactly what i wanted

One thing im not sure i understand, doesn't values just generate a unique list? i.e Values('Customer List'[Region]) would create a table for the unique region names?

[OwenAuger]

Yes that's right. Here, the VALUES function returns a one-column table of region(s) for the currently filtered customers. If just one customer is selected then it will just be one row.
The purpose of this is to retain a filter on this region, while clearing all other Customer filters with ALL.