Needing help with a report building

[appsac1]

Hello! 
I and a beginner in PowerBI and was tasked to help with building a report to identify the tallying of the Net amounts collected from business contracts. 
There will be 3 excel data with the same fields, but different values and content. 

The goal here is to tally the Net fee collected data 'A' and 'B' with the master data, seperated by their types and ID.

Example of the datas

The first data named 'A':

Second named 'B':

Master data:

'A' and 'B' differ in terms of the types of cash flow for each contract included in the documents. 

Data in 'A' and 'B' are supposed to be included in the master data, however there could be the events where they are missing.

I am to show if the net amounts in 'A' and 'B' ('NetCndCry') are tallied with the one in the master data. 

*With the condition that they are categorised uniquely by the other fields in the document.

ObjectID, Flow Type Name (type of cash flow) will be the 2 main identifier for the resutls. *

The report's data sources (mainly A and B) needs to be constantly changed for different business sources, therefore i do not know if coding DAX will affect the change (file name etc), but the fields will always be the same. 

I have tried with a code something like that:

but i do not think I have the intended result i wanted.

A simple table / matrix to show if they are all tallied to the master data would suffice.
Please advise thank you!

[vs_7]

Hi @appsac1 ,

follow below steps

To achieve your goal of tallying the net amounts collected from business contracts in Power BI, you can follow these steps:

1. Import all three Excel datasets (A, B, and the master data) into Power BI as separate tables.

2. Create relationships between the tables based on the common fields such as ObjectID and Flow Type Name. This will enable you to link the data together.

3. Create a new calculated column in the master data table to calculate the total net amount for each contract. You can use the following DAX formula as an example:

   less

• Total Net Amount = CALCULATE(SUM('Master Data'[NetCndCry]), ALLEXCEPT('Master Data', 'Master Data'[ObjectID]))

• Create a new table or matrix visual in Power BI to display the results. Place the ObjectID and Flow Type Name fields in the rows section of the visual.

• Add the following measures to the values section of the visual:

  • Net Amount (A) = SUM('A'[NetCndCry])
  • Net Amount (B) = SUM('B'[NetCndCry])
  • Total Net Amount = SUM('Master Data'[Total Net Amount])

• Compare the Net Amount (A) and Net Amount (B) measures with the Total Net Amount measure to determine if they are tallied. You can use conditional formatting or other visual cues to highlight any discrepancies.