Forum Discussion
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!
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_ctDMBIYl0aqb3SvdnT6XAhamCcg?e=dXjgI5
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
v-xicaiCommunity Support
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_ctDMBIYl0aqb3SvdnT6XAhamCcg?e=dXjgI5
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.