Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Contributor III

## Calculating and explaining cost difference - FX rate and base pay increase

Hello,

I've got a table with following columns:

ID ReportPeriod Region Country Compensation LCY Compensation USD
1 2022-01 APAC China 1000 153.86
1 2022-02 APAC China 1010 155.40
2 2022-01 APAC Japan 257884 2114.64
2 2022-02 APAC Japan 257884 2000
3 2022-01 APAC China 1000 153.86
3 2022-02 APAC China 1000 154.60
4 2022-01 APAC Japan 257884 2114.64
4 2022-02 APAC Japan 257884 2000
5 2022-01 APAC China 1000 153.86
5 2022-02 APAC China 1000 154.60
6 2022-01 APAC Japan 257884 2114.64
6 2022-02 APAC Japan 300000 2326.63

I want to be able to show in a pivot table differences in cost and reason for these.

I calculate month to month difference using below set analysis and it works fine:

[LCY Difference]:

Sum([Compensation LCY])-

[USD Difference]:

Sum([Compensation USD])-

But I would also like to be able to show which changes are related to Base Pay Increase and which FX Rate Adjustment.

FX Rate adjustment happens when there is no difference in local currency, but in USD only, and my expression looks like this:

Base Pay Increase happens when there is difference in local currency, so I assumed below expression:

Unfortunately neither of those gives me correct results... I was wondering if anyone has any suggestions how to correctly write those.

Thank you!

Labels (3)

• ### Set Analysis

Partner - Contributor III

Check if it works for you.

Table: Pivot

Fields in script: ID,
Date( Date#(ReportPeriod,'YYYY-MM'),'YYYY-MM') as Report_Date,
Region,
Country,
"Compensation LCY" as LCY,
"Compensation USD" as USD

Master measures: 1. Change in LCY = Sum(LCY)-above(Column(1))

2. Change in USD = Sum(USD)-above(Column(2))

Other measures: 3. FX Rate = if([Change in LCY]=0,[Change in USD])

4. Base Pay Increase = if([Change in LCY]>0,[Change in USD])

Tags
Community Browser