Double Headers on Matrix

[dejadpower]

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!

[dejadpower]

Thanks , I'm new to powerbi, Can you please share how to split the table with power query

[BITomS]

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.

[dejadpower]

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 ?

[BITomS · Accepted solution]

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.

[dejadpower]

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

[BITomS]

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!

[dejadpower]

Hi Thanks for helping.

for displaying like this:

I need to display qty and cost. Cat1 , Cat2, Cat3 is the Flag column. Now it displays correctly. The only issue is , I need to apply BG color to the top header . Specific column section is not displaying the Flag column in the choice list. It only shows the Values columns. The Flag column is now put in the Columns section of Matrix.

I have searched a lot, There is an option to give bg color to second level of headings only. Is it possible to give bg color to the top level heading in matrix?

Thanks for any help!

[BITomS]

@dejadpower I don't think this can be done currently. However, you can make the suggestion to Microsoft. It looks like someone has already raised this as an idea, so you can vote for it. If it gets enough votes, MS will review the request: https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=66db66d4-67eb-4464-a8f2-6b387a3f7e4f