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.
I'm trying to get my slicer to Filter out some Sales Revenue in Canadian dollors. My Goal is to to display any Sales outside of the local Currency. I created a quick measure "Filter by Value", but I need to get a way to have this based on my Region Slicer. screen shots and code is below. Ideally when I check off Canada on the Slicer the vale of the Canadain Dollor box should be $0.00
As you can tell I'm no expert, but would appreiacte any feed back on this.
Hi @FormworkFan ,
Here are the steps you can follow:
1. Create measure.
True =
var _select=SELECTCOLUMNS('Combined Divison Report',"1",'Combined Divison Report'[Region])
var _column=CONCATENATEX(FILTER(ALL('Combined Divison Report'),'Combined Divison Report'[Region] in _select),'Combined Divison Report'[Region],",")
return
IF(
CONTAINSSTRING(
_column,"Canada") =TRUE(),
SUMX(
FILTER(ALL('Combined Divison Report'),
'Combined Divison Report'[Region] in _select && CONTAINSSTRING('Combined Divison Report'[Region],"Canada") =TRUE()),'Combined Divison Report'[42000 -Sales Revenue Amount]),0)
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Your Solution works, unitl I add a Date Slicer into my Report. It is taking in all of the Data and will not filter by Date. How can I get these new Measure to work with my Date Slicer?
thanks
Hi,
This seems to be working as I need. I'm still playing around with it though.
hi, @FormworkFan
try below measure
canadian sales =
if( selectedvalue('yourtable'[region])="canada",calculate( sum('combined division report'[420000 - sales revenue amount])),0.00)
Did i answer your question? Mark my post as a solution which help other people to find fast and easily.
Thank you for the reply and help ont this
This Kind of works when I only have that one Region "Canada" Selected on my slicer. If I have Multiple Regions selected including the Canada Region, the value is coming as $0.00.
This result is kind of giving me the opposite of what I had at the start.
I placed some screen shots below and as an FYI I broke my Canada into two Regions (East and West). So Will need to add this into there some how. perhaps like an OR Statement, so if I select Eastern or Western or both.
yourtable'[region])="Eastern Canada"
try below
if( selectedvalue('yourtable'[region])=CONTAINSSTRING(values(yourtable'[region]),"canada"),calculate( sum('combined division report'[420000 - sales revenue amount])),0.00)
use only "canada" instead of "easteran canada"
No Luck on that.
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 |
---|---|
51 | |
27 | |
24 | |
13 | |
8 |
User | Count |
---|---|
74 | |
56 | |
47 | |
16 | |
12 |