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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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