Forum Discussion
Helper IIICalculating Measures by Searching text in another table
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
Helper IIIAnonymous 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.
Helper IIIAnonymous 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?