Forum Discussion
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 ?
My PBI file :-
Paul
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.
14 Replies
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 , As per your formula this number is correct. (YTD- LYTD)/LYTD
- SpoilerAs in most of the time , in the past LYTD never have -ve value. But this time it have -ve value.
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
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.
Anonymous
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
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.
Anonymous
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
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.
Anonymous
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
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.
Anonymous
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 :-
Paul
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.
