Forum Discussion
Double Headers on Matrix
Hi There,
I need to create double headers on matrix. I have searched a lot in the community, nothing matches our scenario: My visual should look like this:
But my table doesnt have specific column for categorizing opening and purchase sections.
my table contains the following columns:
| Item | ItemCode | OpenQty | OpenVal | PurQty | PurValue |
i am calculating unit rate using measure (without adding calculated column in the table)
how can i detect OpenQty and OpenVal and OpenUnitRate(measure col) to be displayed under "Opening" and the rest under "Purchase" ?
Thanks for any help!
Hi dejadpower
I think one potential option (although I suspect not the only solution) would be to split your single fact table into 2 (i.e. one for Opening and the other for Purchase). You can do this through Power Query.
You could then create a new column in each table with a standard Id (i.e. Opening = 1 and Purchase = 2), and add a dimension table with the Id and name which joins to these created columns.
Then you can add the dimension name column to the 'Columns' section of your matrix visual and any values or measures calculated for each table should fall under one of the headers.
Hope that helps.
8 Replies
Hi dejadpower
I think one potential option (although I suspect not the only solution) would be to split your single fact table into 2 (i.e. one for Opening and the other for Purchase). You can do this through Power Query.
You could then create a new column in each table with a standard Id (i.e. Opening = 1 and Purchase = 2), and add a dimension table with the Id and name which joins to these created columns.
Then you can add the dimension name column to the 'Columns' section of your matrix visual and any values or measures calculated for each table should fall under one of the headers.
Hope that helps.
Hi
I created seperate tables by spliting the original table and added a new Flag column for each category. Then I created a dimension table using the Id, Qty, Cost and Flag . Now in visual if i add Flag to column in matrix, it displays categorized qty , cost , Id successfully.
Is there any possibilty of giving background color to the Flag column alone ? (pls refer the screenshot in original question)
Thanks for helping
Hi dejadpower
FYI - there is no need to add Qty and Cost in your dimension table as these are 'facts' not dimensions. You can read more about the difference here: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema#star-schema-overview
Glad to hear it's working now. On the background colour, yes, you should be able to update this from the 'Specific Column' section of the visualisation > format visual pane:
If this answers your question, please consider accepting it as the solution. Kudos are also appreciated!
Thanks , I'm new to powerbi, Can you please share how to split the table with power query
First, you can duplicate the table query:
Then remove the Purchase related columns from one table and the Opening related columns from the other:
This gives you the 2 Fact tables.
Thank you for your time, I'll try and update
"You could then create a new column in each table with a standard Id (i.e. Opening = 1 and Purchase = 2), and add a dimension table with the Id and name which joins to these created columns."
-- kindly clarify , should i add new column in both tables ?
