Dynamic Measure based on location

[theandrewswan]

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!

[v-xicai · Accepted solution]

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.

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.