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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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:

regards

1 ACCEPTED SOLUTION
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.

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.

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

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.

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
Anonymous
Not applicable

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

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
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:

the result:

regards

Super User

@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

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Fabric Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors