Forum Discussion

jvirgi's avatar
jvirgi
Icon for Helper III rankHelper III
6 years ago
Solved

Calculating Measures by Searching text in another table

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:

RatingSentimentAllSports
40.6Hockey, Frisbee, Golf
50.3Golf, Tennis
30.2Rugby, Hockey
50.5Golf
30.3Football
50.1Football, Golf

 

Table 2: 

Sports
Hockey
Frisbee
Golf
Tennis
Rugby
Football
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      jvirgi
      Icon for Helper III rankHelper III
      6 years ago

      Anonymous 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's avatar
        jvirgi
        Icon for Helper III rankHelper III
        6 years ago

        Anonymous   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?