sum of values based on different text in another column - dynamic measure

[natabird3]

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.

[v-gizhi-msft]

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:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[natabird3]

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.

[v-gizhi-msft]

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

[natabird3]

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?

Category =

var t = MinX(ADDCOLUMNS({",","/","."},"Facebook",FIND([Value],'Traffic Source'[Source / Medium],1,999999)),[Facebook])

REturn LEFT([Source / Medium],IF(t= 999999,LEN('Traffic Source'[Source / Medium]),t-1))

[amitchandak]

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])

[natabird3]

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.

[amitchandak · Accepted 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))

[natabird3]

Exactly, what i needed thank you.