How to calculate Average figures?

[#1]

Hi All,

I have the following table:

load * Inline
[
Region, ExchangeRate,LocalAmount
US, 1
Brazil,1.7
US,,100
Brazil,,100

]

I concatenate the ExchangeRate and LocalAmount into one table.

Then ExchangeRate is based on USD.

Now I want to show the total USD Amount in one pivot table.

But when I use this expression sum(LocalAmount)/Avg(ExchangeRate), but it show the wrong numbers.

However, if I choose one region US or Brazil, the number is right, so how to calculate this total USD number?

I have attached the QVW doc.

Thanks.

[#2]

I might be misunderstanding here (specially the average portion).

From what I understood you have following need:

1. You have atleast two regions i.e. US and Brazil

2. You have the exchange rate. (1 for US and 1.7 for Brazil)

3. You have amount in local currency that was earned (since it is +ve) for the region.

4. Now you need to get the amount for the region in US dollars. (LocalAmount/ExchangeRate)

Try with straight table first:

1. Create a straight table.

2. Set dimension as Region.

3. Expression 1 = Sum(ExchangeRate), Expression 2 = Sum(LocalAmount), Expression 3 = column(2) /column(1), Expression 4 = Sum(LocalAmount) / Sum(ExchangeRate)

For Pivot:

1. Create the pivot chart.

2. Dimension is Region.

3. Expression is Sum(LocalAmount) / Sum(ExchangeRate)

What I dont understand is the reason for average.

[#3]

Thanks for your reply, why I use average exchangeRate, because there are lot of ExchangeRate based on day, I just

List one exchage rate record for each country.

[#4]

Another thing is: Even if I use your expression:

Sum(LocalAmount) / Sum(ExchangeRate), the result is also wrong.

For example:

If you choose Region BR and US together: the figure is: 74, the right number is: 158

How to figure it out?

[#5]

Shows me right in my straight table. Also in the expressions tab i had to change the Total mode to Sum of Rows.

Also can u set dimension as region in your chart?

If I am still misunderstanding can u post the latest file again?

Best Regards,
Vishal

[#6]

I can not add Region dimension, I just want to show the total USD amount in Pivot table not straight table.

[#7 · Accepted solution]

Here you go: sum(aggr(Sum(LocalAmount)/Avg(ExchangeRate),Region))

In your original file the calc that was being done was 200 / ((1+1.7)/2) = 148.148. What is actually needed is (100/1) + (100/1.7) = 158.82.

I hope this helps. Let me know if it does not.

Best Regards,
Vishal