cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
theandrewswan Frequent Visitor
Frequent Visitor

Dynamic Measure based on location

Hi all - I have the following columns in my table:

 

[Project_ID]

[Open_Location]

[Close_Location]

[Sales_Amount]

 

The possible values for [Open_Location] and [Close_Location] are the same, call them Location 1, Location 2, Location 3

For any given [Project ID], there can be any combination of [Open_Location] and [Close_Location] values. For example:

 

Project ID = 1X46

Open_Location = Location 1

Close_Location = Location 3

Sales_Amount = 5000

 

or

 

Project ID = 3B22

Open_Location = Location 1

Close_Location = Location 1

Sales_Amount = 1000

 

I would like to have a slicer showing Location 1, Location 2 and Location 3. Based on the selection, I would like a [Total Location Sales Amount] measure that sums Sales_Amount where EITHER [Open_Location] OR [Close_Location] equals the value I've chosen in my slicer.

 

With the two examples above, if I selected "Location 1" in my slicer, [Total Location Sales Amount] would equal 1000+5000=6000 (there should be no double-counting). If I selected "Location 3" in my slicer, [Total Location Sales Amount] would equal 5000

 

Any suggestions? Thanks in advance!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamic Measure based on location

Hi @theandrewswan 

 

You can create new calculate table SlicerTable to get the slicer selections, and then create measure Total Location Sales Amount to meet your demand.

 

SlicerTable = DISTINCT(UNION(SELECTCOLUMNS(Table1,"Location",[Open_Location]),SELECTCOLUMNS(Table1,"Location",[Close_Location])))

 

Total Location Sales Amount = IF(MIN(Table1[Open_Location])IN VALUES(SlicerTable[Location]) || MIN(Table1[Close_Location]) IN VALUES(SlicerTable[Location]),SUM(Table1[Sales_Amount]),BLANK())

 

When make some selections in slicer, return the result showing picture below.

 

11.png12.png

 

 

 

 

 

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVBhfsc2lJNLr-1_-4...

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Dynamic Measure based on location

Hi @theandrewswan 

 

You can create new calculate table SlicerTable to get the slicer selections, and then create measure Total Location Sales Amount to meet your demand.

 

SlicerTable = DISTINCT(UNION(SELECTCOLUMNS(Table1,"Location",[Open_Location]),SELECTCOLUMNS(Table1,"Location",[Close_Location])))

 

Total Location Sales Amount = IF(MIN(Table1[Open_Location])IN VALUES(SlicerTable[Location]) || MIN(Table1[Close_Location]) IN VALUES(SlicerTable[Location]),SUM(Table1[Sales_Amount]),BLANK())

 

When make some selections in slicer, return the result showing picture below.

 

11.png12.png

 

 

 

 

 

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVBhfsc2lJNLr-1_-4...

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
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.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors