Survey evaluation: column with multiple answers

[DAX_n00b]

Afternoon everyone,

I am trying to calculate the percentage for each individual answer (a to h) stored as their combination in column I8:

The idea is to create 8 measures (one for each letter) where the number of occurences divided by the number of rows should get the desired outcome. However, my best effort led to this and it gives no results. 
Funnily enough, it gives no error either:

e_perc:=
DIVIDE(

CALCULATE(

COUNTROWS(Survey),

ISNUMBER(FIND("e", Survey[I8]))

),

COUNTROWS(Survey)

)

The data sample seen above should give e_perc = 50%.
Have tried it with asterisks FIND("*e*" , no luck.

If the source data could be kept as they are without unpivotting and splitting the I8 answer to individual lines... that would be much-much appreciated.

[Anonymous · Accepted solution]

Hi @DAX_n00b ,

I created the table and copied your code to a Calculate Column to get the error that the use of the FIND function is not supported, so I won't get any results.

Here is how I did it, first you can create a new ContainE column to count the number of occurrences of the letter "e".

ContainsE = IF(CONTAINSSTRING('Survey'[I8], "e"), 1, 0)

Then you can write a new column E_Percentage to calculate the percentage of occurrences of the letter e in all rows, which will give you the result of 50%.

E_Percentage = 
VAR E_Count = SUMX(FILTER('Survey','Survey'[ContainsE] = 1),1)
VAR Total_Rows = COUNTROWS('Survey')
RETURN
FORMAT(DIVIDE(E_Count,Total_Rows),"0%")

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

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