Reconciling two columns - what function to use?

[Anonymous]

Hi,

I'm currently trying to create the "Company B value" column in the table below.

We are combining the transactions of Company A and Company B into the same fact table.

Company 600 is receiving money from company 700. In the first line we can see that 600 has received the value of £2000 from company 700. In the second line, we can see the transcation from the perspective of company 700, where £2000 has left their account. I need to calculate the Diff column to show inconsistencies in the accounts, which is easy once I have the Company B Value col. Has anyone got a clue how to create this?  

Company A ID	Company B ID	Company A Value	Company B Value	Diff
600	700	£2000	(£2000)	£0
700	600	(£2000)	£2000	£0
600	700	£1000	(£900)	£100
700	600	(£900)	£1000	(£100)

[v-yadongf-msft · Accepted solution]

Hi @Anonymous ,

This is my test table:

Please try following DAX to create new columns:

Company A value = IF('Company'[Company A ID]=600,FORMAT('Company'[Company A transaction],"£#"),"("& FORMAT('Company'[Company A transaction],"£#") &")")

Company B value = IF('Company'[Company B ID]=600,FORMAT('Company'[Company B transaction],"£#"),"("& FORMAT('Company'[Company B transaction],"£#") &")")

Diff = 
VAR Diff1 =ABS([Company A transaction]-[Company B transaction])
VAR Diff2 = FORMAT(Diff1,"£#0")
VAR Diff3 = IF([Company A transaction]<[Company B transaction],"("&Diff2&")",Diff2)
return Diff3

Then you will get results you want:

Please feel free to let me know if I misunderstood your demands.

Best regards,

Yadong Fang

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

[Daryl-Lynch-Bzy]

Hi @Anonymous - can i consider the shaping your data the following way: 

Company Code	Affiliate Code	Company Amount	Affilate Amount	Difference	Absolute Difference
600	700	2,000.00	-2,000.00	0.00	0.00
700	600	-2,000.00	2,000.00	0.00	0.00
600	700	1,000.00	-900.00	100.00	100.00
700	600	-900.00	1,000.00	100.00	100.00
600	700	-1,200.00	1,000.00	-200.00	200.00
700	600	1,000.00	-1,200.00	-200.00	200.00
			Total divide by 2	-100.00	300.00