calculate text column average

[amardeepBI]

Hi 

how we get the average of column which contains only text in Power BI. 

https://1drv.ms/x/s!Ai3rGPgy20kLg-h0HjiktfFWpeDGhQ?e=VyTAzy 

In attached sheet column name "Activities" needs to be calculate the average by Region and country as well 

[v-chenwuz-msft · Accepted solution]

Hi @amardeepBI ,

You mean only contain text without number?

Maybe you can try the following code to create a new column:

if_contain_text =
VAR _cur =
    LEN( [Activities] )
VAR _s0 =
    SUBSTITUTE( [Activities], "0", "" )
VAR _s1 =
    SUBSTITUTE( _s0, "1", "" )
VAR _s2 =
    SUBSTITUTE( _s1, "2", "" )
VAR _s3 =
    SUBSTITUTE( _s2, "3", "" )
VAR _s4 =
    SUBSTITUTE( _s3, "4", "" )
VAR _s5 =
    SUBSTITUTE( _s4, "5", "" )
VAR _s6 =
    SUBSTITUTE( _s5, "6", "" )
VAR _s7 =
    SUBSTITUTE( _s6, "7", "" )
VAR _s8 =
    SUBSTITUTE( _s7, "8", "" )
VAR _s9 =
    SUBSTITUTE( _s8, "9", "" )
VAR _final =
    LEN( _s9 )
RETURN
    IF( _cur - _final > 0, 0, 1 )

This column is used to determine if it contains only text.

And in the page, you can use a table visual to dispaly the result.(By the way, I have made some changes to Switzeland of the table you provided,so the result is 0.33)

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@amardeepBI , Try a measure like

averageX(values(table[region]),calculate(countrows(Table)))