Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Thats perfect! Thank you so much for this. Never worked with Unpivot, so this is a great intoduction.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |