Forum Discussion
Power BI matrix table
Hello,
my issue is, that I need the columns set up in a particular way, which is easy in excel, doable in tableau, but I would need the same in Power BI. So I have quarters and some categories with values, and my goal is to have one category, under that the quarters and the corresponding values.
For now Power BI gives me the opposite possibility, where the columns are the quarters and the categories are divided into the quarters as on the sample picture.
And what I would need is something like this
My users explicitly want to see it this way and needs to be in Power BI as I'm unable to connect to the data source through Tableau.
Is there a way to do it?
Ok, see if this helps.
1) Create a table which reflects the structure you need for the header. In my example I've merged the columns for quearter from the calendar table with a table created using "enter data" in Power Query with the names of the measures to be included in the headers.
The M code for this is:
let Source = #"Calendar Table", #"Removed Other Columns" = Table.SelectColumns(Source,{"Quarter", "QuarterNum"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Quarter"}), MeasureHeader = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS1WitWJVlJVyC9LLVIISSxKTy1Rio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MHeader = _t]), #"Changed Type" = Table.TransformColumnTypes(MeasureHeader,{{"MHeader", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each #"Added Index"), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"MHeader", "Index"}, {"MHeader", "Index"}), #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Quarter", Order.Ascending}}) in #"Sorted Rows"To get
Leave this table unrelated in the model
2) Create the measures you wish to display (the measures listed in the header table) using TREATAS following the equivalent to:
Sales MH = CALCULATE ( [Sum of Sales], TREATAS ( VALUES ( 'Matrix Header'[QuarterNum] ), 'Calendar Table'[QuarterNum] ) )3) Create the final measure to use in the matrix with:
Matrix final measure = SWITCH ( SELECTEDVALUE ( 'Matrix Header'[MHeader] ), "Sales", [Sales MH], "% over Target", FORMAT ( [% over Target], "#0,0%" ) )(You will need the FORMAT function if you are mixing number types. If not, you can just format the measure with the regular number type)
4) Create the matrix visual using:
Rows: whatever field from the model is needed
Columns: the fields from the Matrix Header Table you have created
Values: the [Matrix final measure]
To get:
I've attached the sample PBIX file for you
6 Replies
You can rearrange the fields in the column area of the matrix visual to show the percentages first and the quarter second.
Trying to make Power BI behave like Excel or Tableau is a fallacy. Tell your users that. Instead, embrace the differences. Focus on the business problem you are trying to solve, not on some preconceived notions of how the page should look like.
Thanks for the response. Sadly convincing them is not an option. I need to find a way, even if it would mean to pull the data in Power BI and transfer them into Tableau
Ok, see if this helps.
1) Create a table which reflects the structure you need for the header. In my example I've merged the columns for quearter from the calendar table with a table created using "enter data" in Power Query with the names of the measures to be included in the headers.
The M code for this is:
let Source = #"Calendar Table", #"Removed Other Columns" = Table.SelectColumns(Source,{"Quarter", "QuarterNum"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Quarter"}), MeasureHeader = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS1WitWJVlJVyC9LLVIISSxKTy1Rio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MHeader = _t]), #"Changed Type" = Table.TransformColumnTypes(MeasureHeader,{{"MHeader", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each #"Added Index"), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"MHeader", "Index"}, {"MHeader", "Index"}), #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Quarter", Order.Ascending}}) in #"Sorted Rows"To get
Leave this table unrelated in the model
2) Create the measures you wish to display (the measures listed in the header table) using TREATAS following the equivalent to:
Sales MH = CALCULATE ( [Sum of Sales], TREATAS ( VALUES ( 'Matrix Header'[QuarterNum] ), 'Calendar Table'[QuarterNum] ) )3) Create the final measure to use in the matrix with:
Matrix final measure = SWITCH ( SELECTEDVALUE ( 'Matrix Header'[MHeader] ), "Sales", [Sales MH], "% over Target", FORMAT ( [% over Target], "#0,0%" ) )(You will need the FORMAT function if you are mixing number types. If not, you can just format the measure with the regular number type)
4) Create the matrix visual using:
Rows: whatever field from the model is needed
Columns: the fields from the Matrix Header Table you have created
Values: the [Matrix final measure]
To get:
I've attached the sample PBIX file for you
Hi bundipapa ,
According to your screenshot, I think you add [Type] in Matrix Rows, add [Quarters] in Matrix Columns and [Discount%] and [Margin%] in Matrix Values.
I think [Discount%] and [Margin%] should be calculated columns or measures. What your matrix looks like is based on your data model. If you want to get the result you want, It is better for you to add a [Percentage] column which contains "Discount%" and "Margin%" as value. Then add [Percentage] and [Quarters] in Matrix Columns. [Percentage] is the higher level in column hierachy.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Wouldn't this mean, that I have to duplicate the dataset, so that I have one column for both discount and margin and add a category specifying, if the given value is one or the other?
