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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

percent change formula... please help to correct my approach

hi all,

below is sample data and my formula which is showing wrong figures, required to calculate country wise percent change for every row (show in Table visual) than sum country wise to show in another graph visual.
here is a screenshot (on left) which shows a row with 0.12 without country name when created after sum of pct change.

 

sample data:
date         country         dollar_price   Index
4/1/2000 Argentina                    2.5         1
4/1/2001 Argentina                    2.5         2
4/1/2002 Argentina    0.798722045         3
4/1/2003 Argentina    1.423611111         4
5/1/2004 Argentina    1.477966102         5
6/1/2005 Argentina    1.639627201         6
4/1/2000 Australia      1.541666667         7
4/1/2001 Australia      1.515151515         8
4/1/2002 Australia      1.612903226         9
4/1/2003 Australia      1.863354037       10
5/1/2004 Australia      2.272727273       11
6/1/2005 Brazil           2.393703343       12
1/1/2006 Brazil           2.741543224       13
5/1/2006 Brazil           2.777175092       14
1/1/2007 Brazil           2.999765643       15
6/1/2007 Brazil           3.606900157       16
6/1/2008 Brazil           4.733055661       17

 

code (measure within corresponding table):
Pct Change =
VAR CurrentValue = CALCULATE(VALUES('Big Mac Index2'[dollar_price]))
VAR PreviousValue =
CALCULATE( MAX('Big Mac Index2'[dollar_price]), FILTER('Big Mac Index2', 'Big Mac Index2'[Index]=EARLIER('Big Mac Index2'[Index])-1) )
RETURN
DIVIDE(
CurrentValue - PreviousValue,
PreviousValue
)

note: can't format the code 

 

screenshot:

Core2Plus_0-1623137508126.png

 

 

regards

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

Hi @Anonymous ,

 

There is nothing wrong with your formula.

 

Please check that your Countries table contains all the country names in the Big Mac Index2 table. I see that your country2 column contains "Britain", but the Country Name column doesn't, which may be the cause of the row with 0.12. If the country name is missing, a situation like the following occurs.

 

image.pngimage.png

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

6 REPLIES 6
v-kkf-msft
Community Support
Community Support

Hi @Anonymous ,

 

There is nothing wrong with your formula.

 

Please check that your Countries table contains all the country names in the Big Mac Index2 table. I see that your country2 column contains "Britain", but the Country Name column doesn't, which may be the cause of the row with 0.12. If the country name is missing, a situation like the following occurs.

 

image.pngimage.png

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

Fowmy
Super User
Super User

@Anonymous 

In your formula, I see "Country 2" field but you are using Country name in the visual. Use only one column.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@Fowmy 

nop, please you can see in below screenshot in which i have marked pct change column there is country2 in visual too.

Fowmy
Super User
Super User

@Anonymous 

You the following meaure:

Pcnt Change = 
var __currentcountry  = MAX('Big Mac Index2'[Country])
var __currentdate = MAX('Big Mac Index2'[Date])
var __currvalue = CALCULATE(MAX('Big Mac Index2'[Dollar_Price]), 'Big Mac Index2'[Date] =__currentdate)
var __previousdate = 
    MAXX( 
        FILTER(
            ALL('Big Mac Index2'),
            'Big Mac Index2'[Country] = __currentcountry &&'Big Mac Index2'[Date] < __currentdate 
        ),
        'Big Mac Index2'[Date]
    )
var __prevalue = CALCULATE( MAX('Big Mac Index2'[Dollar_Price] ) , 'Big Mac Index2'[Country] = __currentcountry , 'Big Mac Index2'[Date] = __previousdate )
return
IF(
    __prevalue <> BLANK() ,__currvalue - __prevalue
)
     

Fowmy_0-1623139916636.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

thanks @Fowmy for the code, i have implemented it but don't know why its not working for me, below are 2 screenshot, first one is for code i copy/paste and changed only column names where needed, 2nd screenshot showing result which is blank pct change.

the code:

Core2Plus_0-1623142803685.png

 

the result:

Core2Plus_1-1623142993803.png

 

regards

@Anonymous 



You have not added the date in the visual, you can try this measure, I added ALL( BIGMAC...)  in the last calculate function

Pcnt Change = 
var __currentcountry  = MAX('Big Mac Index2'[Country])
var __currentdate = MAX('Big Mac Index2'[Date])
var __currvalue = CALCULATE(MAX('Big Mac Index2'[Dollar_Price]), 'Big Mac Index2'[Date] =__currentdate)
var __previousdate = 
    MAXX( 
        FILTER(
            ALL('Big Mac Index2'),
            'Big Mac Index2'[Country] = __currentcountry &&'Big Mac Index2'[Date] < __currentdate 
        ),
        'Big Mac Index2'[Date]
    )
var __prevalue = CALCULATE( MAX('Big Mac Index2'[Dollar_Price] ) , 'Big Mac Index2'[Country] = __currentcountry , 'Big Mac Index2'[Date] = __previousdate , ALL('Big Mac Index2') )
return
IF(
    __prevalue <> BLANK() ,__currvalue - __prevalue
)
        

 

 
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.