My expression return wrong value ( This expression is working fine in Sales Table )

[admin11]

Hi All

_Below expression look okay , But it return wrong % :-

_Vari_%_NP = if(isblank( divide(

[_YTD_NP_SGD]

-

[_LYTD_NP_SGD]

,

[_LYTD_NP_SGD]

)),1, divide(

[_YTD_NP_SGD]

-

[_LYTD_NP_SGD]

,

[_LYTD_NP_SGD]

))

Row 1 last year 273k this year 16K it display -94% is wrong , May i know where go wrong ?

My PBI file :-

https://www.dropbox.com/s/71bn5d95o9ufdb0/PBT_V2021_392%20TI_SI_GL%20vari%20return%20wrong%20value.p...

Paul

[v-yangliu-msft]

Hi  @admin11  ,

Sorry, I just reply now

Here are the steps you can follow：

1. Create measrue.

111 =
IF( [_YTD_NP_SGD] > [_LYTD_NP_SGD] ,"+ ve％" , "- ve％")

flag =
IF(
CONTAINSSTRING([111],"- ve%"),1,0)

2. Select [111], select Conditional formatting – Background color

3. Enter the Background color interface, select Format by-Rules, Based on field- [flag], set the conditions

4. Result.

Does this meet your expected results

Best Regards,

Liu Yang

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

[v-yangliu-msft · Accepted solution]

Hi  @admin11 ,

Here are the steps you can follow：

1. Create measure

Measure 4 = 
IF(
    CONTAINSSTRING([Vari_%_NP yang_liu],"- ve%"),1,0)

 2. Select [Vari_%_NP yang_liu], select Conditional formatting – Background color

3. Enter the Background color interface, select Format by-Rules, Based on field-Measure4, set the conditions

4. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[admin11]

@v-yangliu-msft 

Can i have last request , how to make the Vari % onl display +ve % with out the number ? I have try to modify your expression , not successful.

[admin11]

@v-yangliu-msft 

Thank you very much for your help , it work fine now. Look like mission impossible.

Paul

[v-yangliu-msft]

Hi  @admin11 ,

Sorry, this is my negligence.

This is the Dax function:

Vari_%_NP yang_liu =
Var _1=
IF(
    ISBLANK(
        DIVIDE([_YTD_NP_SGD]-[_LYTD_NP_SGD], [_LYTD_NP_SGD]))
,1,
        DIVIDE([_YTD_NP_SGD]-[_LYTD_NP_SGD],[_LYTD_NP_SGD]))

var _2=
IF(
   [_LYTD_NP_SGD] <0 && [_YTD_NP_SGD]>0,ABS(_1),_1)
Return
IF(
[_YTD_NP_SGD] > [_LYTD_NP_SGD] ,FORMAT(_2,"Percent")&" "&"+ ve％" , FORMAT(_2,"Percent")&" "&"- ve％")

Result：

You can downloaded PBIX file from here.

Does this result match your expected data?

Best Regards,

Liu Yang

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

[admin11]

@v-yangliu-msft 

Thank you very much , it work fine now , But i need to highlight those -ve % number back ground red color , i try and i am unable make it. ( Old expression i am able )

As i cannot bring in the measure to Rule.

My PBI file :-

https://www.dropbox.com/s/6ep2aq720luoxfe/PBT_V2021_393%20TI_SI_GL%20set%20-ve%20red%20back%20ground...

Paul

[v-yangliu-msft]

Hi  @admin11  ,

This is the modified formula:

Vari_%_NP =
Var _1=
IF(
    ISBLANK(
        DIVIDE([_YTD_NP_SGD]-[_LYTD_NP_SGD], [_LYTD_NP_SGD]))
,1,
        DIVIDE([_YTD_NP_SGD]-[_LYTD_NP_SGD],[_LYTD_NP_SGD]))

var _2=
IF(
   [_LYTD_NP_SGD] <0 && [_YTD_NP_SGD]>0,ABS(_1),_1)
Return

IF(
[_YTD_NP_SGD] > [_LYTD_NP_SGD] , _2&" "&"+ ve％" , _2&" "&"- ve％)

Best Regards,

Liu Yang

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

[admin11]

@v-yangliu-msft 

Thank you very much for yout help. I get error msg below :-

My PBI file :-

https://www.dropbox.com/s/ucc893klczo7nny/PBT_V2021_393%20TI_SI_GL%20NP%20vari%20error.pbix?dl=0

Paul Yeo

Paul Yeo

[v-yangliu-msft]

Hi  @admin11  ,

This is the modified formula:

Vari_%_NP =
Var _1=IF(
    ISBLANK(
        DIVIDE([_YTD_NP_SGD]-[_LYTD_NP_SGD], [_LYTD_NP_SGD]))
,1,
        DIVIDE([_YTD_NP_SGD]-[_LYTD_NP_SGD],[_LYTD_NP_SGD]))
return
IF(
   [_LYTD_NP_SGD] <0 && [_YTD_NP_SGD]>0,ABS(_1),_1)

Best Regards,

Liu Yang

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

[admin11]

@v-yangliu-msft

Thank you very much , it work fine now.

except it need small touch up , that is the last row , 

last year lost 215 and this year lost 11 , mean it is doing well, i need this as +ve %.

I want to try to modify the code , but i dont know where to insert the condition. 

Paul

[v-yangliu-msft]

Hi  @admin11  ,

I can't download your pbix file, you can try this formula:

Var _1=if(
isblank(
divide([_YTD_NP_SGD]-[_LYTD_NP_SGD],[_LYTD_NP_SGD]))
 ,1,
divide([_YTD_NP_SGD]-[_LYTD_NP_SGD],[_LYTD_NP_SGD]))

return
if(
[_LYTD_NP_SGD] <0 &&[_YTD_NP_SGD]>0,ABS(_1),_1
)

Best Regards,

Liu Yang

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

[admin11]

@v-yangliu-msft 

Thank you very much for your sharing.

When i try your code. I get error below :-

My PBI file :-

https://www.dropbox.com/s/li8ldej4v2t6t8z/PBT_V2021_392%20TI_SI_GL%20copmute%20vari%20np.pbix?dl=0

Paul

[amitchandak]

@admin11 , As per your formula this number is correct.  (YTD- LYTD)/LYTD

[admin11]

Spoiler

As in most of the time , in the past LYTD never have -ve value. But this time it have -ve value.

@amitchandak 

Can you pls advise me how to modify the expression , so that when LYTD -ve value and YTD value +ve , it will return +ve % ?

Paul Yeo