topic Re: Matrix table visualization and summing based on opposite axis in DAX Commands and Tips

Matrix table visualization and summing based on opposite axis

Frank7 — Wed, 04 Dec 2024 17:13:21 GMT

Hi there! I've been struggeling with two things in my report.

1. I find it hard to change the color of a matrix table for only the diagonally intersecting lines. Is this possible with a certain measure and using conditional formatting with that measure? I have tried but it's complex due to having blank values, is this possible with blank values or if not, is it possible when I show a 0 instead of blank?
To my matrix visual I have manually added a diagonal shape to show the divide and replicate the greyed out cells in Excel but it's obviously not the best solution.

2. My second question is how to calculate within the matrix using cell position or opposite row/matrix numbering. I would like to summerize the values of for example column 2 row 1 with the value of column 1 row 2. And show this value only in the lower diagonal axis. This is to show the net differences if those exist.
Is this even possible in a Matrix visual?

What I have been able to replicate from the first Excel matrix:

My Excel which I am have issues trying to replicate the greyed out fields in the first column and the bottom matrix;

I appreciate any advise 🙂

Re: Matrix table visualization and summing based on opposite axis

lbendlin — Thu, 05 Dec 2024 02:54:47 GMT

1. only if you do crossjoins. Blanks are preferred to 0 for rendering

2. depends on the dimension values for your row and column fields, but should be possible.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Matrix table visualization and summing based on opposite axis

Frank7 — Fri, 06 Dec 2024 18:20:07 GMT

Hi lbendlin,

Thank you for willing to take a look!

1. I have tried to use cross joins, but have been struggeling unfortunately.
2. I have added a replication of my .pbix fle with test data in the following google drive link. I think this is the fastest way to share what I have and the Excel file I'm trying to replicate (see sheet 2 of the Excel file).

The pbix. and Excel file:

https://drive.google.com/drive/folders/1WU5ECWvimSIOpzHr6LWXKN21O-QQcHFm?usp=sharing

Re: Matrix table visualization and summing based on opposite axis

lbendlin — Sat, 07 Dec 2024 01:26:40 GMT

Like this?

Re: Matrix table visualization and summing based on opposite axis

Frank7 — Sat, 07 Dec 2024 12:43:34 GMT

Hi lbendlin,

Exactly! Thank you so much 😊
Were you able to see if my second table to show the differences is possible?

Re: Matrix table visualization and summing based on opposite axis

lbendlin — Sat, 07 Dec 2024 22:49:32 GMT

Yeah, sorry, that's far beyond my capabilities. Maybe someone else can assist you with that.