cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
theandrewswan Occasional Visitor
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

 

Any suggestions? Thanks in advance!

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xicai New Contributor
New Contributor

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.

1 REPLY 1
v-xicai New Contributor
New Contributor

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.