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.
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
Solved! Go to 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
Proud to be a Super User!
Paul on Linkedin.
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
Proud to be a Super User!
Paul on Linkedin.
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
You access Power Query through the option "Transform data" in the ribbon
Proud to be a Super User!
Paul on Linkedin.
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
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!
Hi,
I need the "amount" column on the left, not right.
Exacty as shown in the example I attached.
Thanks
Can you post a depiction of what you are trying to show?
Proud to be a Super User!
Paul on Linkedin.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |