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

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

New Contributor

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