columns to right in crosstab

[yogevz]

hi

how can i transfer columns in crosstab from left to right?

[v-juanli-msft]

Hi @yogevz 

Which is "crosstab"?

Could you show an example?

Best Regards

Maggie

[yogevz]

Matrix..   🙂

[v-juanli-msft]

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

[yogevz]

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

[v-juanli-msft]

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

[v-juanli-msft]

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.