cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
yogevz Regular Visitor
Regular Visitor

columns to right in crosstab

hi

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

6 REPLIES 6
Community Support Team
Community Support Team

Re: columns to right in crosstab

Hi @yogevz 

Which is "crosstab"?

Could you show an example?

 

Best Regards

Maggie

yogevz Regular Visitor
Regular Visitor

Re: columns to right in crosstab

Matrix..   🙂

Community Support Team
Community Support Team

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

yogevz Regular Visitor
Regular Visitor

Re: columns to right in crosstab

no.

i want it to be like this. 

 

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

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.

Community Support Team
Community Support Team

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 148 members 1,843 guests
Please welcome our newest community members: