Forum Discussion

z29n's avatar
z29n
Regular Visitor
1 year ago
Solved

help needed - difference btw columns

Hello there,    I am trying to create a new measure to calculate difference btw two columns in absolute and % for each year.  LYOP and OP are Types.  Could you please help me on this? I do not ho...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Try using the following DAX formula.

    Negative vari = 
var _lyop = CALCULATE(SUM('Table 2'[Rev]), 'Table 2'[Type] = "LYOP")
var _op = CALCULATE(SUM('Table 2'[Rev]), 'Table 2'[Type] = "OP")
RETURN
IF(SELECTEDVALUE('Table 2'[Type]) = "OP", _lyop - _op, BLANK())

     

    Best Regards,

    Wisdom Wu

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

  • z29n's avatar
    z29n
    1 year ago

    no, what I mean is I would like to see the difference OP minus LYOP in a format that either negative or positive based on figures- not only negative ones or not in absolute form. Is it clear? 

z29n's avatar
z29n
Regular Visitor
1 year ago

thanks a lot.. 

Is it possible to make "absolute" to appear only in one column on right hand-side instead of repeating itself? 

 

thanks in advance, appreciated.. 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi z29n ,

Try using the following DAX formula.

Absolut = 
var _lyop = CALCULATE(SUM('Table 2'[Rev]), 'Table 2'[Type] = "LYOP")
var _op = CALCULATE(SUM('Table 2'[Rev]), 'Table 2'[Type] = "OP")
RETURN
IF(SELECTEDVALUE('Table 2'[Type]) = "OP", ABS(_lyop - _op), BLANK())

You can also set the column wrap in format visual. Drag the

Best Regards,

Wisdom Wu

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

  • z29n's avatar
    z29n
    Regular Visitor
    1 year ago

    thanks once more.. 

     

    Unfortunately wrapping didn't work for me somehow. I don't know why but the column is still there 😞

     

    also, I would like to see the negatives as well, not only absolute figures so minus can work for me. 

     

    Thanks again.. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi z29n ,

      After setting the wrap, you need to drag the LYOP right border to hide the absolute data columns.

      If you want to get negative numbers, you don’t use abs function.

      Absolut = 
var _lyop = CALCULATE(SUM('Table 2'[Rev]), 'Table 2'[Type] = "LYOP")
var _op = CALCULATE(SUM('Table 2'[Rev]), 'Table 2'[Type] = "OP")
RETURN
IF(SELECTEDVALUE('Table 2'[Type]) = "OP", _lyop - _op, BLANK())
//IF(SELECTEDVALUE('Table 2'[Type]) = "OP", ABS(_lyop - _op), BLANK())

       

      Best Regards,

      Wisdom Wu

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

      • z29n's avatar
        z29n
        Regular Visitor
        1 year ago

        thanks a lot, now one more thing: I would like to have negative variances as well instead of absolute figures. Can you help me  on that as well? 

         

        thanks in advance..