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
how can i transfer columns in crosstab from left to right?
Matrix.. 🙂
Hi @yogevz
Assume i have data as below and add them to a matrix visual,
do you want the columns headers to show as the following?
row | b2 | b1 | a2 | a1 |
a | ||||
b |
Best Regards
Maggie
no.
i want it to be like this.
total | b2 | b1 | a2 | a1 | row |
3 | 2 | 1 | a | ||
7 | 4 | 3 | b | ||
10 | 4 | 3 | 2 | 1 |
Hi @yogevz
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
Best Regards
Maggie
Hi @yogevz
As tested, it is impossible with a matrix visual in Power bi currently.
You could submit an idea here.
Here is a workaround with power query:
1.right click on 'Table1" and select "duplicate' to get "Table2";
2.
add custom column,
expand custom column
add conditional column
filter rows
ect..
Please see my pbix for more details.
Or open the advanced editor for detailed code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUo0BBKGSrE6UK4RkDCCc5NAsgieEYyXBNOJ4KHIgfUZI7ggSRMwNxnVzmRUO5NR9SYj6Y0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [row = _t, column = _t, value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"row", type text}, {"column", type text}, {"value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Group(#"Changed Type", {"row"}, {{"total", each List.Sum([value]), type number}})), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"row", "total"}, {"Custom.row", "Custom.total"}), #"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Custom", each if [row] = [Custom.row] then [Custom.total] else null), #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [Custom] <> null and [Custom] <> ""), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.row", "Custom.total"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[column]), "column", "value"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Custom", "Total"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Total", "a1", "a2", "b1", "b2", "row"}) in #"Reordered Columns"
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |