- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Cross Check Matrix Visual
Hi Community,
I have a dataset with transactions between companies. Parties (From / To) can both sell and purchase each other.
Data Table (example):
The check I want to perform on the dataset is the following: "the sum of Amounts between pairs (ie, A vs B and B vs A) should be zero". In other words, Revenues of A vs B, must be equal to Costs of B vs A.
I'm trying to figure out how to achieve it through a Matrix visual that allows me to check the mismatches between pair (if any), otherwise returns zero (in case of match).
It's quite easy to configure a Power BI matrix visual like the following, where the amounts measure is a simple "SUM of Amount", and companies (From / To) are put in the row / columns respectively. In this case, to detect where the mismatch is I need to check both rows and columns, and the other indicator is the bottom-right corner (total, must be zero too).
This is not the kind of matrix I'm looking for, I would prefer something like the following, where only the mismatching pairs are visible. In other words, a matrix that shows where SUM of Amount (A vs B) <> SUM of Amount (B vs A), which can be written also as Sum of Amounts (A vs B) + Sum of Amounts (B vs A) <> 0.
In this way, the matrix would be eye catching, showing relevant mismatches only.
Any tip to achieve the above?
Thanks for your kind support
Marco
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I produced your required output of the intercompany mismatch matrix.
In order to make Power BI recognize matching revenue and costs transactions recorded by as identical, I created a helper column like below.
Then I created calculated columns to identify the mismatch.
I will omit details of each step, but instead, I attach the link to the pbix file below.
Intercompany mismatch matrix.pbix
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you @DataNinja777, I do really appreciate your contribution.
It's the result I was looking for. I need to adapt to the actual dataset, since the example table is semplified for better understanding, but it should be easy to do.
Thank you.
Marco
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I produced your required output of the intercompany mismatch matrix.
In order to make Power BI recognize matching revenue and costs transactions recorded by as identical, I created a helper column like below.
Then I created calculated columns to identify the mismatch.
I will omit details of each step, but instead, I attach the link to the pbix file below.
Intercompany mismatch matrix.pbix

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
10-29-2024 05:13 AM | |||
11-28-2024 10:35 AM | |||
08-16-2019 02:25 PM | |||
09-18-2024 10:15 AM | |||
11-27-2024 05:19 AM |
User | Count |
---|---|
141 | |
115 | |
84 | |
63 | |
47 |