right to left matrix

[pbiuser1234]

Hello 

Is there any solution yet to align right to left a matrix?

If not, Is there a way to add a table group header (like in SSRS) to a table visualization?

I understand that in power bi, a group header can only be done with a matrix visualization - but I need to show it right to left.

Thank you 

[PaulDBrown · Accepted solution]

Well this isn't perfect, but it's the closest I have come.

1) In Power Query, create the layout of the matrix using:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdCxCoAwDATQf8ncweYi6OwnOJYiFcRREB38e6GDFMI5JnkcR1ISlSBTubb9OJ+4zPf6De1BckgCRtXRnlE4OjYbZQW00oFRdTR2zOI3FqwBKjVG1VEwCkcjo+Zo+y1jXU1yfgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Amount = _t, #"Sub-Category" = _t, Category = _t]),
    OrignalTable = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}, {"Sub-Category", type text}, {"Category", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(OrignalTable,{"Category"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type),
    CatIndex = Table.RenameColumns(#"Added Index",{{"Index", "CatIndex"}}),
    #"Merged Queries" = Table.NestedJoin(CatIndex, {"Category"}, OrignalTable, {"Category"}, "CatIndex.1", JoinKind.LeftOuter),
    #"Expanded CatIndex.1" = Table.ExpandTableColumn(#"Merged Queries", "CatIndex.1", {"Sub-Category"}, {"Sub-Category"}),
    #"Added Index1" = Table.AddIndexColumn(#"Expanded CatIndex.1", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each [CatIndex] + [Index]/1000),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    SubCatIndex = Table.RenameColumns(#"Removed Columns",{{"Custom", "SubCatIndex"}}),
    #"Removed Other Columns1" = Table.SelectColumns(SubCatIndex,{"Sub-Category", "SubCatIndex"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"SubCatIndex", "CatIndex"}, {"Sub-Category", "Category"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"CatIndex"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"CatIndex", Order.Ascending}})
in
    #"Sorted Rows"

which gets you 

 Create an active relationship between Matrix Category and the orginal table Category. Create an inactive relationship between Matrix Category and the original table SubCategory.

Then these measures:

Sum Value = SUM(OrigTable[Amount])

Amount =
VAR SubCat =
    CALCULATE (
        [Sum Value],
        USERELATIONSHIP ( 'Matrix Structure'[Category], OrigTable[Sub-Category] )
    )
RETURN
    IF (
        ISINSCOPE ( 'Matrix Structure'[Category] ),
        [Sum Value] + SubCat,
        "Total "
            & FORMAT ( DIVIDE ( [Sum Value] + SubCat, 2 ), "#,##0" )
    )

Create a table visual with the field from the matrix table & the [Amount] measure, add conditional formatting and you get:

 I've attached the sample PBIX file

[pbiuser1234]

Wow! thank you very much! I'm new to power bi so I'll try to figure out each step:) where can I see the code of the power query in the pbix file? Thanks again

[PaulDBrown]

You access Power Query through the option "Transform data" in the ribbon

[pbiuser1234]

Hi

To the right it's how the data is set in the table,

To the left is how I want to show it - Exactly how it's shown in a matrix, only right to left. 

Thank you

[v-kelly-msft]

Hi  @pbiuser1234 ,

Put category and subcategory to the Rows field and you will see:

Check my .pbix  file attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[pbiuser1234]

Hi,

I need the "amount" column on the left, not right.

Exacty as shown in the example I attached.

Thanks

[PaulDBrown]

Can you post a depiction of what you are trying to show?