Get sum of transactions from both entites based on selected entity

[alarekawek]

Hello,

This is going to be a very basic question.

I have a table that shows all intercompany transactions from four different systems. It has information about source system, entity number, and counterparty along with reporting numbers of both entities - looks like follows

Source system	Entity	Counterparty	Value	Reporting no Entity	Reporting No Counterparty
V	A	C	100	10	30
V	A	B	200	10	20
J	B	A	(210)	20	10
I	C	A	(110)	30	10

I want to be able to compare values reported by the analysed entity - let's say A with how much other entity is reporting and see the differences - for example in it's system A is reporting 100 with C, but C in it's system is reporting (110). 

Reporting no Entity	Entity	Value entity	Reporting No Counterparty	Counterparty	Value counterparty	Difference
10	A	100	30	C	(110)	(10)
10	A	200	20	B	(210)	(10)

I have a slicer to select "Reporting no" of the entity in question. 

I've tried to use calculate with all function to ignore the filters coming from the 'Reporting no Entity' and based on the selected value to show all balances for the counterparty, but I seem to be missing some part as it does not work correctly - I currently get total for all counterparties like below

Reporting no Entity	Entity	Value entity	Reporting No Counterparty	Counterparty	Value counterparty
10	A	100	30	C	(320)
10	A	200	20	B	(320)

How can I make it show values for individual entities in value counterparty?

[v-junyant-msft · Accepted solution]

Hi @alarekawek ,

Please try this way:
Use this DAX to create a calculated column:

Value counterparty = 
VAR _CurrentCounterparty = [Counterparty]
VAR _CurrentEntity = [Entity]
RETURN
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Entity] = _CurrentCounterparty && 'Table'[Counterparty] = _CurrentEntity
    )
)

Then use this DAX to calculate this difference:

Difference = [Value] - [Value counterparty]

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alarekawek]

Thank you very much, this helps me a lot!