Forum Discussion
Reconciling two columns - what function to use?
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) |
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 Diff3Then 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.
2 Replies
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 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 Diff3Then 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.