COUNTDISTINCT with multiple filters

[Anonymous]

He everyone! I have a big data set I'm working on and I thought I had it but I noticed some weird calculations so I wanted to ask for your help. 

 The simplified dataset I have looks like this (I added the table at the end).  I have a "nested" view of Mobiles operators, a Group ID, its Subsidiaries, the Country of presence, and the Revenue per group. 

Please note that: 

• The revenue stated is per group, corresponding to the second column
• There might be repeated countries at any level (Operator, Group and even Subsidiary)

In the end, what I need is a measure that allows me to see: 

• Average group revenue per country: the value in the last column divided by the number of countries that each group has (unique values) 
• Average subsidiary revenue per country: for this, it would be assumed the total revenue of the group. So is similar to the "Average group revenue per country", but in this case is divided by the number of countries that subsidiary has (unique values)  
• Average operator revenue per country: the sum of all the group revenues that correspond to each mobile operator, divided by the total number of countries that operator has (again, unique values) 

I believe I would have to create several measures but I am a little lost here. Any help will be appreciated! 

Mobile operator	Group ID	Subsidiary	Country	Revenue per group
AT&T	2011	AT&T 1	Mexico	1217800
AT&T	2011	AT&T 1	Mexico	1217800
AT&T	2011	AT&T 1	Belice	1217800
AT&T	2011	AT&T 2	Guatemala	1217800
AT&T	2011	AT&T 2	Mexico	1217800
AT&T	2011	AT&T 2	Mexico	1217800
AT&T	2011	AT&T 3	Belice	1217800
AT&T	2011	AT&T 3	Guatemala	1217800
AT&T	2011	AT&T 3	Guatemala	1217800
AT&T	2011	AT&T 4	Mexico	1217800
AT&T	2011	AT&T 4	Belice	1217800
AT&T	2011	AT&T 4	Belice	1217800
AT&T	2011	AT&T 5	Mexico	1217800
AT&T	2701	Sky  1	Guatemala	5689900
AT&T	2701	Sky  2	USA	5689900
AT&T	2701	Sky  2	USA	5689900
AT&T	2701	Sky  2	USA	5689900
AT&T	2701	Sky  3	Belice	5689900
AT&T	2701	Sky  3	Belice	5689900
AT&T	2701	Sky  3	USA	5689900
AT&T	2701	Sky  4	Belice	5689900
AT&T	2701	Sky  4	Belice	5689900
AT&T	2701	Sky  4	Belice	5689900
AT&T	2701	Sky  5	Guatemala	5689900
AT&T	2701	Sky  5	Guatemala	5689900
AT&T	2701	Sky  5	Guatemala	5689900
AT&T	2701	Sky  6	USA	5689900
AT&T	2701	Sky  7	USA	5689900
AT&T	2701	Sky  7	USA	5689900
AT&T	2701	Sky  7	USA	5689900
Telcel	1252	Telcel 1	Mexico	1233330
Telcel	1252	Telcel 1	Belice	1233330
Telcel	1252	Telcel 1	Guatemala	1233330
Telcel	1252	Telcel 2	Guatemala	1233330
Telcel	1252	Telcel 2	Belice	1233330
Telcel	1252	Telcel 2	Belice	1233330
Telcel	1252	Telcel 3	Belice	1233330
Telcel	1252	Telcel 3	USA	1233330
Telcel	1252	Telcel 3	USA	1233330
Telcel	1252	Telcel 4	Belice	1233330
Telcel	1252	Telcel 5	Guatemala	1233330
Telcel	1252	Telcel 6	USA	1233330
Telcel	1252	Telcel 6	Mexico	1233330
Telcel	1252	Telcel 6	Mexico	1233330
Telcel	1253	Telefonos celulares 1	Belice	2326600
Telcel	1253	Telefonos celulares 1	USA	2326600
Telcel	1253	Telefonos celulares 1	USA	2326600
Telcel	1253	Telefonos celulares 2	Belice	2326600
Telcel	1253	Telefonos celulares 2	Belice	2326600
Telcel	1253	Telefonos celulares 2	Belice	2326600
Telcel	1253	Telefonos celulares 3	Guatemala	2326600
Telcel	1253	Telefonos celulares 3	USA	2326600
Telcel	1253	Telefonos celulares 3	USA	2326600
Telcel	1253	Telefonos celulares 4	USA	2326600
Telcel	1253	Telefonos celulares 4	Belice	2326600
Telcel	1253	Telefonos celulares 4	Belice	2326600
Telcel	1253	Telefonos celulares 5	USA	2326600
Telcel	1253	Telefonos celulares 5	Belice	2326600
Telcel	1253	Telefonos celulares 5	Belice	2326600
Telcel	2353	Movisrar 2	Guatemala	3400000
Telcel	2353	Movisrar 2	Mexico	3400000
Telcel	2353	Movisrar 2	Mexico	3400000
Telcel	2353	Movistar 1	Mexico	3400000
Telcel	2353	Movistar 1	Mexico	3400000
Telcel	2353	Movistar 1	Mexico	3400000
Telcel	2353	Movistar 3	Belice	3400000
Telcel	2353	Movistar 3	Guatemala	3400000
Telcel	2353	Movistar 3	Guatemala	3400000
T-Mobile	1121	Open Mobile 1	Mexico	2341677
T-Mobile	1121	Open Mobile 1	USA	2341677
T-Mobile	1121	Open Mobile 1	Mexico	2341677
T-Mobile	1121	Open Mobile 2	Guatemala	2341677
T-Mobile	1121	Open Mobile 2	Mexico	2341677
T-Mobile	1121	Open Mobile 2	Mexico	2341677
T-Mobile	1121	Open Mobile 3	Belice	2341677
T-Mobile	1121	Open Mobile 3	Guatemala	2341677
T-Mobile	1121	Open Mobile 3	Guatemala	2341677
T-Mobile	1121	Open Mobile 4	Guatemala	2341677
T-Mobile	1121	Open Mobile 5	USA	2341677
T-Mobile	1121	Open Mobile 6	USA	2341677
T-Mobile	1121	Open Mobile 7	Mexico	2341677
T-Mobile	1121	Open Mobile 7	Belice	2341677
T-Mobile	1283	MetroPCS 1	Guatemala	2585000
T-Mobile	1283	MetroPCS 2	USA	2585000
T-Mobile	1283	MetroPCS 2	USA	2585000
T-Mobile	1283	MetroPCS 3	USA	2585000
T-Mobile	1283	MetroPCS 3	Mexico	2585000
T-Mobile	1283	MetroPCS 4	Mexico	2585000
T-Mobile	1283	MetroPCS 4	Guatemala	2585000
T-Mobile	1283	MetroPCS 5	Guatemala	2585000
T-Mobile	1283	MetroPCS 5	Belice	2585000
T-Mobile	1283	MetroPCS 6	USA	2585000
T-Mobile	1283	MetroPCS 7	Belice	2585000
T-Mobile	1283	MetroPCS 7	Mexico	2585000
T-Mobile	1283	MetroPCS 8	Belice	2585000
T-Mobile	1283	MetroPCS 8	Guatemala	2585000
T-Mobile	1283	MetroPCS 9	Guatemala	2585000
T-Mobile	1283	MetroPCS 9	Belice	2585000
T-Mobile	2234	T-Mobile USA 1	USA	687000
T-Mobile	2234	T-Mobile USA 1	Mexico	687000
T-Mobile	2234	T-Mobile USA 2	USA	687000
T-Mobile	2234	T-Mobile USA 2	Mexico	687000
T-Mobile	2234	T-Mobile USA 2	USA	687000
T-Mobile	2467	Triton 1	Guatemala	12375900
T-Mobile	2467	Triton 1	Mexico	12375900
T-Mobile	2467	Triton 2	Belice	12375900
T-Mobile	2467	Triton 2	USA	12375900
T-Mobile	2467	Triton 3	Mexico	12375900
T-Mobile	2467	Triton 3	Belice	12375900
T-Mobile	2467	Triton 4	Mexico	12375900
T-Mobile	2467	Triton 4	Belice	12375900
T-Mobile	2467	Triton 5	Guatemala	12375900
T-Mobile	2467	Triton 5	Guatemala	12375900
T-Mobile	2467	Triton 6	Belice	12375900

[Greg_Deckler]

@Anonymous You can use COUNTROWS(DISTINCT(SELECTCOLUMNS(...)))

[Anonymous]

Hi @Greg_Deckler this seems to work! but...

Now that it needs to interact with other visualization on the page, I'm afraid is not working correctly as a measure. How could I calculate this as a column? 

I tried calculating the number of countries in a column with this:

Number of countries = CALCULATE(COUNTROWS(DISTINCT(SELECTCOLUMNS(Operators, [Country]))), Operators[Group ID]=Operators[Group ID])

But for some reason I'm getting only a value of 1 in all the cells 

[Greg_Deckler · Accepted solution]

@Anonymous Right, because you are in row context so there is only 1 country per row. Not sure you should be doing it as a column but if you do you would need something like:

Number of countries = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(ALL(Operators), Operators[Group ID]=EARLIER(Operators[Group ID])),"Country",[Country])))