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

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