How to combine to matrix visuals

[mp390988]

Hi,

Is there a solution or a way that I can combine to seperate matrix visuals together that share the same column headers? So for example, in this image below, you can see I have two different matrix visuals and I want to combine them together in one as they both share the same column headers. But I still want to maintain the totals rows as it is for the first matrix. I don't want that to be removed.

Effectively, I want to replicate this which is in Excel:

Thank You

[SharfiDirar]

Create new table that lists all the rows you want to display in your matrix , including  total, and KPI rows like RevTargets. Then, write a DAX( SWITCH ) measure that returns the correct calculation for each row based on its type, and add it the Value of the matrix.

[v-karpurapud]

Hi @mp390988 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[v-karpurapud]

Hi @mp390988 

We haven't received a response to our last message and wanted to follow up to see if you have found a solution. If you still need help, please share more details so we can assist you further.

Thank you.

[v-karpurapud]

Hi @mp390988 

Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to @Ritaf1983 , @MohamedFowzan1 , @rohit1991  and @danextian  for offering helpful suggestions.

could you please confirm if this has resolved your issue by suggested solutions? If you are still encountering any difficulties, please inform us and we will be glad to assist further.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[mp390988]

I got this close:

considering this is what I had before:

but my issue is that I need to get my Totals row before Target, Variance and % and I also need a couple of blank rows. Right now my Variance is giving the wrong result because it is defined as [Revenue] - [Target] but I can't create my own custom totals row (=[Revenue]) therefore variance is not being calculated correctly.

[danextian]

You will need to disable the total row and a custom total row to the column you're using. Total will always be on the bottom or top depending on the visual. You can conditionally format the text color or the background to highlight the total. 

total background =
IF ( tbl[tradetype2] = "Total", "#eeeeee" )
--to be used as field value

You will also need to use dynamic format strings to apply a different for mat to %. Example:

IF ( tbl[tradetype2] = "%", "0%", "0,0" )

[danextian]

I've done this many times before, often involving disconnected tables. While it’s technically possible to achieve this using calculation groups, it would require creating numerous calculation items- one for each row, including blanks and totals. That approach quickly becomes cumbersome, especially if the number of items is large or expected to grow over time.

There are custom visuals, like the Inforiver custom matrix, that can do this out of the box. They’re free to install but require a subscription when publishing to the Power BI service (and they aren't cheap).

That said, it would be much easier for us to offer a working solution if you could share sample data we can easily copy and paste into Excel (because no one wants to type it out manually, right?). A sanitized version (confidential data removed) of your PBIX file stored in the cloud would work just as well.

[mp390988]

hi @danextian ,

it's difficult for me to provide you a sample file because my company does not allow for me to upload or send data. Its strict.

[MohamedFowzan1]

Hi @mp390988 

Create a “Row Type” Table
Create a table with these values:
RowType
TradeType
Target
Variance
%

First column ("RowType"):

• TradeType
• TradeType
• TradeType
• TradeType
• TradeType
• TradeType
• TradeType
• TradeType
• Target
• Variance
• %

Second column ("TradeType"):

• Spot
• Forward
• Invoicing
• Option
• Other
• PaymentFee
• Currency Cloud
• TOTAL
• (blank)
• (blank)
• (blank)

The last three (Target, Variance, %) in "RowType" have blank values under "TradeType".

This table acts as a selector for which type of row you want in each part of your matrix.

Calculation Groups :
• If you can create a calculation group for "TradeType/Target/Variance/%".
• Each calculation group item returns the value you want for that row

Not sure how accurate calculation groups will be and if you are able to add it, Please try using the below measure once:

Write a “Unified” Measure with SWITCH

For each “RowType”, write a measure so that trade types show the original measure, but Target, Variance, and % rows show their respective calculations.
Sample:
Use both the columns as selected value if needed.
Combined Matrix Value =
SWITCH(
TRUE(),
SELECTEDVALUE('RowType'[RowType]) = "TradeType", [YourMainMeasure],
SELECTEDVALUE('RowType'[RowType]) = "Target", [Target],
SELECTEDVALUE('RowType'[RowType]) = "Variance", [Variance],
SELECTEDVALUE('RowType'[RowType]) = "%", [%],
BLANK()
)
After adding the rows and columns, try using the above measure in the values in Matrix

Hopefully this thought process works

[Ritaf1983]

Hi @mp390988 
There isn’t a built-in option via the standard UI to combine two separate matrices with shared headers or to create complex row layouts like in Excel.
However, there are two methods to achieve similar results:

1. Using an unrelated table with the desired row structure (including categories like Spot, Forward, TOTAL, Target, Variance, etc.). You can build dynamic DAX measures that return values based on the current row label. This method allows full control over row order and logic.

2. Using calculated groups – a feature that is already available in Power BI (through Field Parameters or Tabular Editor). This allows creating flexible, reusable grouping logic for building hybrid or asymmetrical matrix layouts.

Here are two great resources that demonstrate both techniques:

• How I create a hybrid matrix with custom column names:
  https://medium.com/@ugookoma/how-i-create-a-hybrid-matrix-with-custom-column-names-in-power-bi-8e93d...

• Building a matrix with asymmetrical columns and rows in Power BI:
  https://exceleratorbi.com.au/building-a-matrix-with-asymmetrical-columns-and-rows-in-power-bi/

Additionally, premium visuals like Zebra BI and Inforiver are built specifically to support this kind of advanced layout and dynamic totals.
https://zebrabi.com/power-bi-custom-visuals/tables/

https://inforiver.com/reporting-matrix/

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickl

[mp390988]

Hi @Ritaf1983 ,

Thank you for your response.
I followed one of the blogs you sent and it got me this far:

But the issue is I don't see tTO flowing into my matrix visual though I have declared a variable called tTo (which should display the total).

I have also included this in my HeaderTable as below:

But for some reason it does not show in my visual matrix. It does show when i remove the columns and values from my matrix as per below:

[Ritaf1983]

Hi @mp390988 
To assit i need the access to the pbix , please save it in some public cloude like one dive and share via hyperlink.

[rohit1991]

Hi @mp390988 

To solve the problem follow the steps given below: 

STEP 1: Load the Excel Data

1. Open Power BI Desktop.
2. Click Home >> Get Data > >Excel and load your source file.
3. Select the sheet and click Load.

STEP 2: Unpivot the Monthly Columns

1. Go to Home >> Transform Data to open Power Query Editor.
2. Select all the month columns (e.g., Jan-25 to TOTAL).
3. Click Transform >> Unpivot Columns.
4. Rename:
   • Attribute >>Month
   • Value >> Value

STEP 3: Create a Category Column

This column will identify the type of row: Actual, Target, Variance, or %.

1. Click Add Column >> Custom Column
2. Name: Category
3. Formula:

if [Trade Type] = "Target" then "Target"
else if [Trade Type] = "Variance" then "Variance"
else if [Trade Type] = "%" then "%"
else "Actual"

 STEP 4: Create a TradeTypeSort Column

To sort trade types like Spot, Forward, etc., in your preferred order:

1. Click Add Column >> Custom Column
2. Name: TradeTypeSort
3. Formula:

if [Trade Type] = "Spot" then 1
else if [Trade Type] = "Forward" then 2
else if [Trade Type] = "Option" then 3
else if [Trade Type] = "PaymentFee" then 4
else if [Trade Type] = "Invoicing" then 5
else if [Trade Type] = "Currency Cloud" then 6
else if [Trade Type] = "SWAP" then 7
else if [Trade Type] = "TOTAL" then 8
else if [Trade Type] = "Target" then 9
else if [Trade Type] = "Variance" then 10
else if [Trade Type] = "%" then 11
else 99

4. Click Close & Apply to return to Power BI.

 STEP 5: Sort Columns in Data View

1. Go to Data View
2. Select column Category >> Click Sort by Column >> CategorySort
3. Select column Trade Type >> Click Sort by Column >> TradeTypeSort

 STEP 6: Create the Matrix Visual

1. Insert a Matrix Visual
2. Use the following fields:
   • Rows: Trade Type
   • Columns: Month
   • Values: Value

 Now the matrix will:

• Display data in your custom order
• Include Actuals, Target, Variance, and % in one clean visual

[mp390988]

Hi @rohit1991 ,

Thank you for your response.
Very detailed and thorough. 
Sorry, I didn't make it clear but my source is a live connection to an exisitng power bi semantic model and not an excel source. But I need to acheive the same layout as per the excel file. Also, Target, Variance and % are not values in the TradeType dimension. Hope that makes sense. Does this change anything?

Thank You