cancel
Showing results for
Did you mean:
Frequent Visitor

## Comparing to other customers in region

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Comparing to other customers in region

@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

Proud to be a Datanaut!

3 REPLIES 3
Super Contributor

## Re: Comparing to other customers in region

@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

Proud to be a Datanaut!

Frequent Visitor

## Re: Comparing to other customers in region

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?

Super Contributor

## Re: Comparing to other customers in region

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.

Proud to be a Datanaut!

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors