cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
djh Frequent Visitor
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
OwenAuger Super Contributor
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
OwenAuger Super Contributor
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

djh Frequent Visitor
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?

 

OwenAuger Super Contributor
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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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

January 2020 Community Highlights

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

Difinity Conference

Difinity Conference

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

Top Solution Authors
Top Kudoed Authors