Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 r...
DAX
  • v-yadongf-msft's avatar
    v-yadongf-msft
    4 years ago

    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.

v-yadongf-msft's avatar
v-yadongf-msft
Community Support
4 years ago

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.