Calculate percentage occurrence of word in a dataset for new table

[codebrane]

If I have a dataset imported from a sharepoint list ("Please Choose" is the default Choice value where a grade was not required):

userid,grade1,grade2,grade3,grade4

123,Excellent,Please Choose,Please Choose,Good

123,Please Choose,Please Choose,Satisfactory,Unsatisfactory

123,Excellent,Unsatisfactory,Unsatisfactory,Good

123,Excellent,Good,Unsatisfactory,Good
124,Excellent,Good,Unsatisfactory,Good
...

is it possible using DAX (or something else) to work out the percentage of "Unsatisfactory" for a userid, ignoring all occurrences of "Please Choose"? So the percentage would relate to the total of "Unsatisfactory" vs the total of "Excellent"+"Good"+"Satisfactory"+"Unsatisfactory".

The percentage would be stored in a new "Fitness" table. I can then filter on the value of "Fitness" in a report:

userid,fitness
123,33
124,56
...

For the above example, there are 12 grade values for the userid "123", 4 of which are "Unsatisfactory" (ignoring "Please Choose"), so the new "Fitness" table would contain "33" for the userid "123" and so on.

thanks,

Alistair

[parry2k · Accepted solution]

@codebrane there are couple of steps you need to do, first unpivot your table, here is query with unpivoted data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQy1nGtSE7NyUnNK9EJyElNLE5VcM7Izy9OReO55+enKMXqQLTgUxicWJJZnJaYXJJfVKkTmleMxIXrR1iJqgCdi2IpQhNIGI9SE6KUxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"userid,grade1,grade2,grade3,grade4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"userid,grade1,grade2,grade3,grade4", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "userid,grade1,grade2,grade3,grade4", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"userid,grade1,grade2,grade3,grade4.1", "userid,grade1,grade2,grade3,grade4.2", "userid,grade1,grade2,grade3,grade4.3", "userid,grade1,grade2,grade3,grade4.4", "userid,grade1,grade2,grade3,grade4.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"userid,grade1,grade2,grade3,grade4.1", Int64.Type}, {"userid,grade1,grade2,grade3,grade4.2", type text}, {"userid,grade1,grade2,grade3,grade4.3", type text}, {"userid,grade1,grade2,grade3,grade4.4", type text}, {"userid,grade1,grade2,grade3,grade4.5", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"userid,grade1,grade2,grade3,grade4.1", "userid"}, {"userid,grade1,grade2,grade3,grade4.2", "grade1"}, {"userid,grade1,grade2,grade3,grade4.3", "grade2"}, {"userid,grade1,grade2,grade3,grade4.4", "grade3"}, {"userid,grade1,grade2,grade3,grade4.5", "grade4"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"userid"}, "Grade", "Value")
in
    #"Unpivoted Other Columns"

Now add following measures , you can club all these in one measure as well

Total Unsatisfactory = CALCULATE( COUNTROWS( Table1 ), Table1[Value] = "Unsatisfactory" )

Total Others = CALCULATE( COUNTROWS( Table1 ), Table1[Value] <>   "Please Choose"  )

% = DIVIDE( [Total Unsatisfactory], [Total Others] )

And here is the result

[codebrane]

thanks for that @parry2k . I'm new to power bi so I don't know what the first bit is. Where does the string come from in

Binary.FromText

[parry2k]

@codebrane that i just created a table, go to query editor, in left pane, right click and choose blank query, click advanced editor and paste the code.  Once it is done, you wil see a table with all the steps to unpivot table which you can replicate on your original table.

[codebrane]

thanks for taking the time to help with this @parry2k , very much appreciated.