Forum Discussion

marcofalzone's avatar
marcofalzone
Helper I
2 years ago
Solved

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

 

 

  • 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

     

2 Replies

  • 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

     

  • 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