Summing Multiple IF Statements to get a score

[reyno1j]

Hi All,

I am trying to calculate a score for each row within a table that contain a mark in various columns (example below):

Image:

Table:

Account	Red	Green	Blue	Small	Medium	Large	Score
[1]	[2]	[3]	[1]	[2]	[3]
1	X			X			2
2		X		X			3
3		X			X		4
4	X					X	4
5			X			X	6

So, if a column has an X in it, it needs to add the number underneath the column header for all the columns to give an overall total score.  I oringally was do an if statement for each column saying IF column = X THEN column score ELSE 0 and then add them all together, but I couldn't get it to work.

Can anyone help?

Thanks in advance

Jon

[v-jianboli-msft · Accepted solution]

Hi @reyno1j ,

Unpivot could help you achieve your goal.

Please try:

First, unpivot your data:

Output:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NK1HSUQpKTQGS7kWpqXlA2imnNBVIBecm5uQAad/UlMzSXCDDJ7EoPVUpVidayRDIiwBiBShGsEGyRmiiqLLGGKIwNkjWBE0GoQIka4rVTrBsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Account", Int64.Type}, {"Red", type text}, {"Green", type text}, {"Blue", type text}, {"Small", type text}, {"Medium", type text}, {"Large", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Account"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Then add a new column:

Create a measure:

Measure = IF(ISINSCOPE('Table'[Attribute]),MAX('Table'[Value]),CALCULATE(SUM('Table'[Score]),FILTER('Table',[Value]<>BLANK())))

Apply to the matrix visual:

Turn off Row subtotals and rename Column subtotals:

Final output:

Best Regards,

Jianbo Li

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

[reyno1j]

Thats perfect!  Thank you so much for this.  Never worked with Unpivot, so this is a great intoduction.