Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |