Calculated dimension

[rerebecky]

Hi I am would like to create a table showing revenue based on number of countries where companies have sales.

No of countries we have sales	revenue
1	5K
2	10K
3	20K

However, my approach could not show when I want to see.

I have field: "SalesCountries', "Revenue", "CompanyName".

And I create a variable called "NoOfCountries", with express Count(Distinct([SalesCountries]))

Then I created a calculated dimension with this expression:

Aggr(SUM(IF(NoOfCountries=1,'1',
IF(NoOfCountries=2,'2',
IF(NoOfCountries=3,'3',
IF(NoOfCountries=4,'4',
IF(NoOfCountries=5,'5',
IF(NoOfCountries>=6 and NoOfCountries <=10,'6-10',
IF(NoOfCountries>=6 and NoOfCountries <=15,'11-15',
IF(NoOfCountries>=15 and NoOfCountries <=20, '16-20',
IF(NoOfCountries>=20 and NoOfCountries <=25, '21-25',
'above 25')))))))))),CompanyName)

However, It doesn't work as expected and end up showing column with a list of countries.

can anyone help me solve this issue please?

[tresesco]

Could you share a sample data set with expected output?

[rerebecky]

Data:

CompanyName	SalesCountries	YTD revenue
A	China	100
A	Germany	100
A	Thailand	100
A	Japan	100
A	Korea	100
B	Korea	100
B	Japan	100
C	China	100
D	China	100
E	China	100
F	China	100
F	Thailand	100
F	Japan	100
G	US	100
G	China	100

expected output:

No of Countries Sales	Revenue
5	500.0
4	0
3	300
2	400
1	300
Above 6	0