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 how to code DAX. 

 

thanks in advance.. 

 

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? 

10 Replies

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