Power BI: How to summarize values?

Hi Shruthi_21 

Load the survey data from Excel into Power BI and click Transform data to open Power Query Editor. Then transform the data to get your expected output.

You can create a blank query and open Advanced editor, then paste below codes to replace the default one to check the steps. Also attach the pbix for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVJBDoIwEPxK03MT3oAkeFMTvREOtCzSCF3TFhN+b6uJkUoRLk0mszO7O9uioBcAYmUPJiE7xJtUV8roSWNC3ENMi3eHUyHAGMllJ+2YkAzVA5QEJYCWLOKxoGH0kMaFuXZVdTeSHnoO2nFZN/A44Y2cH3s/eSV8RwnmRXxBRveItRtk0AYC5Eun7NlWTdP8LTvaFvTybNF9Pi1mYvDC8AohXug5G+yWtDeaBKHHNl7zJ7ae//dCk5uvU5VP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Str1 Category" = _t, #"Str2 Category" = _t, #"Str3 Category" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Str1 Category", type text}, {"Str2 Category", type text}, {"Str3 Category", type text}}),
    #"Categories" = Table.FromList(List.Distinct(List.Combine({#"Changed Type"[Str1 Category],#"Changed Type"[Str2 Category],#"Changed Type"[Str3 Category]}))),
    #"Grouped Str1" = Table.Group(#"Changed Type", {"Str1 Category"}, {{"Str1 Count", each Table.RowCount(_), Int64.Type}}),
    #"Grouped Str2" = Table.Group(#"Changed Type", {"Str2 Category"}, {{"Str2 Count", each Table.RowCount(_), Int64.Type}}),
    #"Grouped Str3" = Table.Group(#"Changed Type", {"Str3 Category"}, {{"Str3 Count", each Table.RowCount(_), Int64.Type}}),
    #"Merged Table 1" = Table.Join(Categories, {"Column1"}, #"Grouped Str1", {"Str1 Category"}, JoinKind.LeftOuter),
    #"Merged Table 2" = Table.Join(#"Merged Table 1", {"Column1"}, #"Grouped Str2", {"Str2 Category"}, JoinKind.LeftOuter),
    #"Merged Table 3" = Table.Join(#"Merged Table 2", {"Column1"}, #"Grouped Str3", {"Str3 Category"}, JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Table 3",{"Str1 Category", "Str2 Category", "Str3 Category"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Str1 Count", "Str2 Count", "Str3 Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Column1", "Strength Category"}, {"Str1 Count", "Strength 1"}, {"Str2 Count", "Strength 2"}, {"Str3 Count", "Strength 3"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Score", each 3 * [Strength 1] + 2 * [Strength 2] + 1 * [Strength 3])
in
    #"Added Custom"

What's more, if you want to show the output in a table visual in the report, we can use DAX method to realize it. 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.