cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

columns to right in crosstab

hi

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

6 REPLIES 6
Highlighted
Community Support
Community Support

Re: columns to right in crosstab

Hi @yogevz 

Which is "crosstab"?

Could you show an example?

 

Best Regards

Maggie

Highlighted
Helper II
Helper II

Re: columns to right in crosstab

Matrix..   🙂

Highlighted
Community Support
Community Support

Re: columns to right in crosstab

Hi @yogevz 

Assume i have data as below and add them to a matrix visual,

15.png

 

 

do you want the columns headers to show as the following?

row b2 b1 a2 a1
a        
b        

 

Best Regards

Maggie

Highlighted
Helper II
Helper II

Re: columns to right in crosstab

no.

i want it to be like this. 

 

totalb2b1a2a1row
3  21a
743  b
104321 
Highlighted
Community Support
Community Support

Re: columns to right in crosstab

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:

6.png

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.

Highlighted
Community Support
Community Support

Re: columns to right in crosstab

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors