Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
reyno1j
Frequent Visitor

Summing Multiple IF Statements to get a score

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:

reyno1j_0-1666084426260.png

Table:

AccountRedGreenBlueSmallMediumLargeScore
[1][2][3][1][2][3]
1X  X  2
2 X X  3
3 X  X 4
4X    X4
5  X  X6

 

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

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @reyno1j ,

 

Unpivot could help you achieve your goal.

Please try:

First, unpivot your data:

vjianbolimsft_1-1666145994591.png

Output:

vjianbolimsft_2-1666146009161.png

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:

vjianbolimsft_3-1666146098170.png

Create a measure:

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

Apply to the matrix visual:

vjianbolimsft_4-1666146167422.png

Turn off Row subtotals and rename Column subtotals:

vjianbolimsft_5-1666146195906.png

Final output:

vjianbolimsft_6-1666146239803.png

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.

View solution in original post

2 REPLIES 2
v-jianboli-msft
Community Support
Community Support

Hi @reyno1j ,

 

Unpivot could help you achieve your goal.

Please try:

First, unpivot your data:

vjianbolimsft_1-1666145994591.png

Output:

vjianbolimsft_2-1666146009161.png

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:

vjianbolimsft_3-1666146098170.png

Create a measure:

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

Apply to the matrix visual:

vjianbolimsft_4-1666146167422.png

Turn off Row subtotals and rename Column subtotals:

vjianbolimsft_5-1666146195906.png

Final output:

vjianbolimsft_6-1666146239803.png

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.

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

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors