cancel
Showing results for
Did you mean:
Highlighted
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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Dynamic Measure based on location

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.

Best Regards,

Amy

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

Community Support Team

## Re: Dynamic Measure based on location

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.

Best Regards,

Amy

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

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.

#### 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

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