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.
Hello all,
I am having an issue, which i was thinking would be quite easy to solve but happened to be difficult creating a measure for. I want to create a few measures that specificly segment my different sales channels by a keyword. On a website when you buy a product it can come from Facebook, but also Facebook ref, Facebook int, etc. I would like to create a measure that will filter all traffic coming from "Facebook". If i wanted to get teh value in a filter is quite easy, advance filter and contains facebook, however how could i make this as a measure dynamically, as i would like to add a few of those in a bar chart visual comparing the different channels.
Sample of the data would look like this:
Users | Source Medium |
50000 | Facebook/ref |
40000 | Facebook.int |
235236 | Ebay.dasf |
300 | Amazon.com |
352 | Facebook,safasga |
3500 | Amazon |
2352 | Ebay |
Output i would need the measure to get all users for any facebook mention (in this case 90352), then if i created another measure for the Amazon would be 3800. And potentially i would need one measure that will summarize all the remaining, but i think would be just the difference between the measures created and total.
Hope you can help.
Thanks in advance for the help.
Solved! Go to Solution.
You can try
Facebook =
var _sel = "Facebook"
return
calculate(sum(Table[User]),Filter(all(Table),search(_sel,Table[Source Medium] ,1,0)>0))
OR
Facebook =
var _sel = "Facebook"
return
calculate(sum(Table[User]),Filter((Table),search(_sel,Table[Source Medium] ,1,0)>0))
Hi,
According to your description, please take following steps:
1)Create a slicer table first:
2)Try this measure:
Measure =
SUMX (
DISTINCT ( 'Table'[Source Medium] ),
IF (
LEFT (
'Table'[Source Medium],
LEN ( SELECTEDVALUE ( 'Slicer Table'[Slicer Value] ) )
)
= SELECTEDVALUE ( 'Slicer Table'[Slicer Value] )
&& SELECTEDVALUE ( 'Slicer Table'[Slicer Value] ) <> BLANK (),
CALCULATE ( SUM ( 'Table'[Users] ) )
)
)
When you select one value in slicer, the result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
Dear Giotto @v-gizhi-msft ,
Thanks for the reply, however i didnt want to create a slicer as what i want to do is compare the values in a bar chart side by side. So what i would need is a distinct measure that will give me the value for each (key term that i am seraching in the source medium column), not a slicer that can go from one to the other, sorry but this is not solving my problem.
Hi,
Please try this calculated column:
Category =
var t = MinX(ADDCOLUMNS({",","/","."},"Position",FIND([Value],'Table'[Source Medium],1,9999)),[Position])
REturn LEFT([Source Medium],IF(t= 9999,LEN('Table'[Source Medium]),t-1))
Choose this column and [Users] as a clustered bar chart, it shows:
Hope this helps.
Best Regards,
Giotto Zhi
Dear Giotto @v-gizhi-msft,
Maybe its just me, but where in the solution i am selecting which value to filter? This is displaying everything as if i put source medium column or what does it do?
Try like
measure =
var _sel = allselected(values(Table[Source Medium]))
return
calculate(count(Table[User]),Filter(all(Table),search(_sel,Table[Source Medium] ,1,0)>0))
measure =
var _sel = maxx(allselected(Table),(Table[Source Medium]))
return
calculate(count(Table[User]),Filter(all(Table),search(_sel,Table[Source Medium] ,1,0)>0))
Better you move source medium to a new table and then join it use this kind of filter. Else all will remove all filters
Source = distinct (Table[Source Medium])
Thank you for the quick reply @amitchandak , however in the proposed solution i do not understand how do i filter for say facebook or amazon values which are in the source/medium column? I am trying to select the actual user values for each of the specific terms, not a count of how many times it appears in the source medium column, hope this explains better.
You can try
Facebook =
var _sel = "Facebook"
return
calculate(sum(Table[User]),Filter(all(Table),search(_sel,Table[Source Medium] ,1,0)>0))
OR
Facebook =
var _sel = "Facebook"
return
calculate(sum(Table[User]),Filter((Table),search(_sel,Table[Source Medium] ,1,0)>0))
Exactly, what i needed thank you.
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |