Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 |