Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
admin11
Memorable Member
Memorable Member

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

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 ?

admin11_0-1616665166239.png

My PBI file :-

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

 

Paul

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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

v-yangliu-msft_0-1617254540582.png

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

v-yangliu-msft_1-1617254540589.png

4. Result.

v-yangliu-msft_2-1617254540590.png

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.

View solution in original post

14 REPLIES 14
v-yangliu-msft
Community Support
Community Support

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

v-yangliu-msft_0-1617947083837.png

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

v-yangliu-msft_1-1617947083841.png

4. Result.

v-yangliu-msft_2-1617947083842.png

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
Community Support
Community Support

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

v-yangliu-msft_0-1617254540582.png

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

v-yangliu-msft_1-1617254540589.png

4. Result.

v-yangliu-msft_2-1617254540590.png

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.

@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_0-1617441787870.png

 

@v-yangliu-msft 

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

Paul

v-yangliu-msft
Community Support
Community Support

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:

v-yangliu-msft_0-1617182915997.png

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.

@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 )

admin11_0-1617199180799.png

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
Community Support
Community Support

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.

@v-yangliu-msft 

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

admin11_0-1617173261518.png

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
Community Support
Community Support

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.

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

admin11_0-1617098241613.png

Paul

v-yangliu-msft
Community Support
Community Support

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.

 

@v-yangliu-msft 

Thank you very much for your sharing.

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

admin11_0-1617020680178.png

My PBI file :-

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

 

Paul

amitchandak
Super User
Super User

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.