Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

COUNTDISTINCT with multiple filters

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 operatorGroup IDSubsidiaryCountryRevenue per group
AT&T2011AT&T 1Mexico1217800
AT&T2011AT&T 1Mexico1217800
AT&T2011AT&T 1Belice1217800
AT&T2011AT&T 2Guatemala1217800
AT&T2011AT&T 2Mexico1217800
AT&T2011AT&T 2Mexico1217800
AT&T2011AT&T 3Belice1217800
AT&T2011AT&T 3Guatemala1217800
AT&T2011AT&T 3Guatemala1217800
AT&T2011AT&T 4Mexico1217800
AT&T2011AT&T 4Belice1217800
AT&T2011AT&T 4Belice1217800
AT&T2011AT&T 5Mexico1217800
AT&T2701Sky  1Guatemala5689900
AT&T2701Sky  2USA5689900
AT&T2701Sky  2USA5689900
AT&T2701Sky  2USA5689900
AT&T2701Sky  3Belice5689900
AT&T2701Sky  3Belice5689900
AT&T2701Sky  3USA5689900
AT&T2701Sky  4Belice5689900
AT&T2701Sky  4Belice5689900
AT&T2701Sky  4Belice5689900
AT&T2701Sky  5Guatemala5689900
AT&T2701Sky  5Guatemala5689900
AT&T2701Sky  5Guatemala5689900
AT&T2701Sky  6USA5689900
AT&T2701Sky  7USA5689900
AT&T2701Sky  7USA5689900
AT&T2701Sky  7USA5689900
Telcel1252Telcel 1Mexico1233330
Telcel1252Telcel 1Belice1233330
Telcel1252Telcel 1Guatemala1233330
Telcel1252Telcel 2Guatemala1233330
Telcel1252Telcel 2Belice1233330
Telcel1252Telcel 2Belice1233330
Telcel1252Telcel 3Belice1233330
Telcel1252Telcel 3USA1233330
Telcel1252Telcel 3USA1233330
Telcel1252Telcel 4Belice1233330
Telcel1252Telcel 5Guatemala1233330
Telcel1252Telcel 6USA1233330
Telcel1252Telcel 6Mexico1233330
Telcel1252Telcel 6Mexico1233330
Telcel1253Telefonos celulares 1Belice2326600
Telcel1253Telefonos celulares 1USA2326600
Telcel1253Telefonos celulares 1USA2326600
Telcel1253Telefonos celulares 2Belice2326600
Telcel1253Telefonos celulares 2Belice2326600
Telcel1253Telefonos celulares 2Belice2326600
Telcel1253Telefonos celulares 3Guatemala2326600
Telcel1253Telefonos celulares 3USA2326600
Telcel1253Telefonos celulares 3USA2326600
Telcel1253Telefonos celulares 4USA2326600
Telcel1253Telefonos celulares 4Belice2326600
Telcel1253Telefonos celulares 4Belice2326600
Telcel1253Telefonos celulares 5USA2326600
Telcel1253Telefonos celulares 5Belice2326600
Telcel1253Telefonos celulares 5Belice2326600
Telcel2353Movisrar 2Guatemala3400000
Telcel2353Movisrar 2Mexico3400000
Telcel2353Movisrar 2Mexico3400000
Telcel2353Movistar 1Mexico3400000
Telcel2353Movistar 1Mexico3400000
Telcel2353Movistar 1Mexico3400000
Telcel2353Movistar 3Belice3400000
Telcel2353Movistar 3Guatemala3400000
Telcel2353Movistar 3Guatemala3400000
T-Mobile1121Open Mobile 1Mexico2341677
T-Mobile1121Open Mobile 1USA2341677
T-Mobile1121Open Mobile 1Mexico2341677
T-Mobile1121Open Mobile 2Guatemala2341677
T-Mobile1121Open Mobile 2Mexico2341677
T-Mobile1121Open Mobile 2Mexico2341677
T-Mobile1121Open Mobile 3Belice2341677
T-Mobile1121Open Mobile 3Guatemala2341677
T-Mobile1121Open Mobile 3Guatemala2341677
T-Mobile1121Open Mobile 4Guatemala2341677
T-Mobile1121Open Mobile 5USA2341677
T-Mobile1121Open Mobile 6USA2341677
T-Mobile1121Open Mobile 7Mexico2341677
T-Mobile1121Open Mobile 7Belice2341677
T-Mobile1283MetroPCS 1Guatemala2585000
T-Mobile1283MetroPCS 2USA2585000
T-Mobile1283MetroPCS 2USA2585000
T-Mobile1283MetroPCS 3USA2585000
T-Mobile1283MetroPCS 3Mexico2585000
T-Mobile1283MetroPCS 4Mexico2585000
T-Mobile1283MetroPCS 4Guatemala2585000
T-Mobile1283MetroPCS 5Guatemala2585000
T-Mobile1283MetroPCS 5Belice2585000
T-Mobile1283MetroPCS 6USA2585000
T-Mobile1283MetroPCS 7Belice2585000
T-Mobile1283MetroPCS 7Mexico2585000
T-Mobile1283MetroPCS 8Belice2585000
T-Mobile1283MetroPCS 8Guatemala2585000
T-Mobile1283MetroPCS 9Guatemala2585000
T-Mobile1283MetroPCS 9Belice2585000
T-Mobile2234T-Mobile USA 1USA687000
T-Mobile2234T-Mobile USA 1Mexico687000
T-Mobile2234T-Mobile USA 2USA687000
T-Mobile2234T-Mobile USA 2Mexico687000
T-Mobile2234T-Mobile USA 2USA687000
T-Mobile2467Triton 1Guatemala12375900
T-Mobile2467Triton 1Mexico12375900
T-Mobile2467Triton 2Belice12375900
T-Mobile2467Triton 2USA12375900
T-Mobile2467Triton 3Mexico12375900
T-Mobile2467Triton 3Belice12375900
T-Mobile2467Triton 4Mexico12375900
T-Mobile2467Triton 4Belice12375900
T-Mobile2467Triton 5Guatemala12375900
T-Mobile2467Triton 5Guatemala12375900
T-Mobile2467Triton 6Belice12375900
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    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])))

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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])))