Cross Check Matrix Visual

[marcofalzone]

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

[marcofalzone]

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

[DataNinja777 · Accepted solution]

Hi @marcofalzone 

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