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? 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi z29n ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the new measure to calculate absolute.

Absolute = 
var _lyop = SUM('Table'[LYOP])
var _op = SUM('Table'[OP])
RETURN
ABS(_lyop - _op)

3.Create the new measure to calculate percentage.

Percentage = 
var _lyop = SUM('Table'[LYOP])
var _op = SUM('Table'[OP])
RETURN
IF(
    ISBLANK(_lyop),
    BLANK(),
    (_op - _lyop) / _lyop * 100
)

4.Drag the measures into the matrix visual. The result is shown below.

You can also view the following documents to learn more information about DAX function.

ABS function (DAX) - DAX | Microsoft Learn

SUM function (DAX) - DAX | Microsoft Learn

ISBLANK function (DAX) - DAX | Microsoft Learn

 

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 for the reply but it did not work out 😞 

    here is the below result.. 

     

    Can you please help me to solve? 

     

    thanks in advance.. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi z29n ,

      Based on the desctiption, please try the following formula again.

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

      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.. 

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

         

        thanks in advance, appreciated..