topic Re: Reconciling two columns - what function to use? in DAX Commands and Tips

Reconciling two columns - what function to use?

Anonymous — Mon, 22 Aug 2022 15:31:24 GMT

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)

Re: Reconciling two columns - what function to use?

Anonymous — Mon, 22 Aug 2022 21:26:19 GMT

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

Re: Reconciling two columns - what function to use?

v-yadongf-msft — Thu, 25 Aug 2022 09:32:52 GMT

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.