Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Exemple of what I have:
Colxxxxx | Colxxxx | Colxxx | Colxxxxx | Produto | Organização | Total amount | Colxxxx |
xxx | xx | xx | xx | A | X | 4 | xx |
xxx | xx | xx | xx | B | Z | 6 | xx |
xxx | xx | xx | xx | C | Y | 5 | xx |
xxx | xx | xx | xx | J | Z | 7 | xx |
xxx | xx | xx | xx | K | W | 3 | xx |
What I need as result -> 1 row for each column with total amount and column name inheritance (text).
Produto | Organização | Total amount |
Produto | Organização | 25 |
Could you guys help me on this one?
Thanks!
Solved! Go to Solution.
Hi lucasconsult,
You can use Unpivot Column, see M code below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqioUNJRQiMcgTgCiE0gArE6ONQ5AXEUEJsRUOcMxJFAbEpAnRfUPHMC6ryBOByIjaHqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Colxxxxx = _t, Colxxxx = _t, Colxxx = _t, Colxxxxx.1 = _t, Produto = _t, Organização = _t, #"Total amount" = _t, Colxxxx.1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Colxxxxx", type text}, {"Colxxxx", type text}, {"Colxxx", type text}, {"Colxxxxx.1", type text}, {"Produto", type text}, {"Organização", type text}, {"Total amount", Int64.Type}, {"Colxxxx.1", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Colxxxxx", "Colxxxx", "Colxxx", "Colxxxxx.1", "Organização", "Total amount", "Colxxxx.1"}, "Attribute", "Value"), #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Colxxxxx", "Colxxxx", "Colxxx", "Colxxxxx.1", "Total amount", "Colxxxx.1", "Attribute", "Value"}, "Attribute.1", "Value.1"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns1",{"Colxxxxx", "Colxxxx", "Colxxx", "Colxxxxx.1", "Colxxxx.1", "Value", "Value.1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "Organização"}, {"Attribute", "Produto"}}) in #"Renamed Columns"
After close&applied, create a measure using DAX:
Total amount_ = SUM(Table1[Total amount])
Then create a table chart which can meet your requirement as below:
Regards,
Jimmy Tao
Hi lucasconsult,
You can use Unpivot Column, see M code below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqioUNJRQiMcgTgCiE0gArE6ONQ5AXEUEJsRUOcMxJFAbEpAnRfUPHMC6ryBOByIjaHqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Colxxxxx = _t, Colxxxx = _t, Colxxx = _t, Colxxxxx.1 = _t, Produto = _t, Organização = _t, #"Total amount" = _t, Colxxxx.1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Colxxxxx", type text}, {"Colxxxx", type text}, {"Colxxx", type text}, {"Colxxxxx.1", type text}, {"Produto", type text}, {"Organização", type text}, {"Total amount", Int64.Type}, {"Colxxxx.1", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Colxxxxx", "Colxxxx", "Colxxx", "Colxxxxx.1", "Organização", "Total amount", "Colxxxx.1"}, "Attribute", "Value"), #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Colxxxxx", "Colxxxx", "Colxxx", "Colxxxxx.1", "Total amount", "Colxxxx.1", "Attribute", "Value"}, "Attribute.1", "Value.1"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns1",{"Colxxxxx", "Colxxxx", "Colxxx", "Colxxxxx.1", "Colxxxx.1", "Value", "Value.1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "Organização"}, {"Attribute", "Produto"}}) in #"Renamed Columns"
After close&applied, create a measure using DAX:
Total amount_ = SUM(Table1[Total amount])
Then create a table chart which can meet your requirement as below:
Regards,
Jimmy Tao
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |