help needed - difference btw columns

[z29n]

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

[v-jiewu-msft · Accepted solution]

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.

[v-jiewu-msft]

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]

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

[v-jiewu-msft]

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]

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

[v-jiewu-msft]

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]

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

[v-jiewu-msft]

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]

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

[v-jiewu-msft · Accepted solution]

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 · Accepted solution]

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?