Calculating Measures by Searching text in another table

[jvirgi]

I'd like to create measures to calculate the counts of Sports within AllSports from Table 1 as well as then average rating and sentiment for each of the Sports in Table 2.  For example for football - the count is 2, avg rating is 4, avg sentiment is 0.2.

I wanted to calculate these measures so that i can then create a bubble chart showing each sport, sized by the count, and with the avg rating and sentiment on my x and y axes.

I don't want to create a measure to count for each sport, because then there may be a ton of measures.

@ImkeF I had seen you had a number of similar solutions for other people but i couldnt figure how to adapt for my case.

Table 1:

Rating	Sentiment	AllSports
4	0.6	Hockey, Frisbee, Golf
5	0.3	Golf, Tennis
3	0.2	Rugby, Hockey
5	0.5	Golf
3	0.3	Football
5	0.1	Football, Golf

Table 2: 

Sports

Hockey

Frisbee

Golf

Tennis

Rugby

Football

[v-yiruan-msft · Accepted solution]

Hi @jvirgi ,

You can create 2 measures for calculating average of rating and sentiment as below:

Avg rating = 
SUMX (
    FILTER (
        'Table 1',
        SEARCH ( MAX ( 'Table 2'[Sports] ), 'Table 1'[AllSports], 1, 0 ) > 0
    ),
    'Table 1'[Rating]
)
    / COUNTROWS (
        FILTER (
            'Table 1',
            SEARCH ( MAX ( 'Table 2'[Sports] ), 'Table 1'[AllSports], 1, 0 ) > 0
        )
    )

Avg sentiment = 
SUMX (
    FILTER (
        'Table 1',
        SEARCH ( MAX ( 'Table 2'[Sports] ), 'Table 1'[AllSports], 1, 0 ) > 0
    ),
    'Table 1'[Sentiment]
)
    / COUNTROWS (
        FILTER (
            'Table 1',
            SEARCH ( MAX ( 'Table 2'[Sports] ), 'Table 1'[AllSports], 1, 0 ) > 0
        )
    )

Best Regards

Rena

[jvirgi]

@v-yiruan-msft Thanks! This is exactly what i was looking for. 

However i notice when i  also try to make a donut chart of the breakout of the Count of sports, i dont get anything when i use Table2[Sports] as my legend and the countrows measure as my Values. 

Not sure if this is why but when i just create a matrix table for Sport, Count of sports, Avg rating, Avg sentiment,  the table totals shows the value from only the final row in the table rather than an average across all rows.

[jvirgi]

@v-yiruan-msft   Actually it was my mistake, the donut chart worked fine... i had it setup wrong.  

But do you know why the matrix table totals show only the final rows value?

[camargos88]

Hi @jvirgi ,

I've created this file as an example: Download PBIX 

I used Power Query to split the sport in new rows.

Ricardo