Summing Multiple IF Statements to get a score

Question

Hi All,&nbsp;I am trying to calculate a score for each row within a table that contain a mark in various columns (example below):&nbsp;Image:Table:AccountRedGreenBlueSmallMediumLargeScore[1][2][3][1][2][3]1X&nbsp;&nbsp;X&nbsp;&nbsp;22&nbsp;X&nbsp;X&nbsp;&nbsp;33&nbsp;X&nbsp;&nbsp;X&nbsp;44X&nbsp;&nbsp;&nbsp;&nbsp;X45&nbsp;&nbsp;X&nbsp;&nbsp;X6&nbsp;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.&nbsp; 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?&nbsp;Thanks in advanceJon

v-jianboli-msft · Accepted Answer

Hi&nbsp;reyno1j&nbsp;,
&nbsp;
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]&lt;&gt;BLANK())))
Apply to the matrix visual:

Turn off Row subtotals and rename Column subtotals:

Final output:

Best Regards,
Jianbo Li
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Summing Multiple IF Statements to get a score

2 Replies

Related content

Multiple IF statements in DAX

Multiple nested IF statements

single dataset refresh, we observe multiple SQL statements

Multiple IF statements

sum multiple columns, how to?

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