Divide Row 2 By Row 1 in a Straight Table

[#1]

I have a list which contains regions and values. I would like to divide a value for each region by the value in region 1 (e.g., "US"). The only result I get is 100% for region 1 ("US") divided by region 1. All the other rows are null.

Region Data % of US

US 20 100%

EU 50

JP 10

Here is an example of the code I am using ...

(Sum (Data)) / (Sum ({$<region={'US'}>}Data))

Any help you can offer would be greatly appreciated. Thank you in advance.

[patrickanderson]

This is the same question I asked above. So I am following.

[johnw · Accepted solution]

Maybe this?

sum(Data)/sum(total {<Region={'US'}>} Data)

I think the problem is that each row is taking the intersection of your set and that row's dimension values. For anything but US, that intersection is null, so the whole expression returns null. By using "total", you tell it to ignore the dimension values when calculating the number on the bottom. If you have other dimensions, though, it might be a little more complicated. Let's say you have a "Data Type" field in the chart. You might need this:

sum(Data)/sum(total <"Data Type"> {<Region={'US'}>} Data)

[#4]

That worked perfectly! Thanks!