How to show the most representative of users country in a project

[Sly]

I have this table
I would like to add a column (desired result) that shows the country with the most users participating in that project for each project
As example, for project 1 I have 3 users in CA and 2 in US. "Desired Result" will then show CA for all users

UsersName		UsersCountry		Project		DesiredResult
User1		CA		Project1		CA
User2		US		Project1		CA
User3		CA		Project1		CA
User4		CA		Project1		CA
User5		CA		Project1		CA
User1		CA		Project2		US
User2		US		Project2		US
User3		CA		Project2		US
User4		US		Project2		US
User6		US		Project2		US
User1		CA		Project3		AU
User3		CA		Project3		AU
User6		US		Project3		AU
User7		AU		Project3		AU
User8		AU		Project3		AU
User9		AU		Project3		AU
User10		AU		Project3		AU
User11		AU		Project3		AU
User12		AU		Project3		AU
User13		AU		Project3		AU

[SpartaBI · Accepted solution]

@Sly 

Desired Result = 
VAR _current_project = Table[Project]
VAR _tbl_1 = 
    FILTER(
        Table,
        Table[Project] = _current_project
    )
VAR _tbl_2 =
    GROUPBY( _tbl_1,Table[UsersCountry],"@Users", SUMX(CURRENTGROUP(), 1))
VAR _max_per_country = MAXX(_tbl_2, [@Users])
VAR _desired_country = 
    CONCATENATEX(
        FILTER(
            _tbl_2,
            [@Users] = _max_per_country
        ),
    Table[UsersCountry], ", "
    )
RETURN
    _desired_country

In case it solved your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂

[SpartaBI · Accepted solution]

For example, let's say in case of a tie, you want to return the country by sorting of it's name. 
So, is there will be a tie between CA and US, than the result will be CA:

Desired Result 2 = 
VAR _current_project = Users[Project]
VAR _tbl_1 = 
    FILTER(
        Users,
        Users[Project] = _current_project
    )
VAR _tbl_2 =
    GROUPBY( _tbl_1,Users[UsersCountry],"@Users", SUMX(CURRENTGROUP(), 1))
VAR _max_per_country = MAXX(_tbl_2, [@Users])
VAR _top_countries = 
    FILTER(
        _tbl_2,
        [@Users] = _max_per_country
    )
RETURN
    MINX(_top_countries,Users[UsersCountry])

[SpartaBI · Accepted solution]

@Sly 

Desired Result = 
VAR _current_project = Table[Project]
VAR _tbl_1 = 
    FILTER(
        Table,
        Table[Project] = _current_project
    )
VAR _tbl_2 =
    GROUPBY( _tbl_1,Table[UsersCountry],"@Users", SUMX(CURRENTGROUP(), 1))
VAR _max_per_country = MAXX(_tbl_2, [@Users])
VAR _desired_country = 
    CONCATENATEX(
        FILTER(
            _tbl_2,
            [@Users] = _max_per_country
        ),
    Table[UsersCountry], ", "
    )
RETURN
    _desired_country

In case it solved your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂

[Sly]

Great! It does exactly want I wanted.

However, when there is a tie, it shows as example CA,US

How can I pick and show only the first one ?

[SpartaBI]

@Sly I did that on porpuse :))
In case there is a tie, who is the first one? 😅
Give me the logic for that and I'll add a new measure for that but will also keep the previous one for community visabilty. Please don't forget to mark them as a solution

[Sly]

I want to use that for communications purpose and use the right language for an email.
As example, If I get US,SP I can either use english or Spanish. 

But I would prefer to use English because it is a "universal language"

I know I can't handle all the exceptions, but I also know that a majority of people can communicate in English.
Also, I know that I have bunch of users without country set (BLANK). So if I end up with a majority displaying BLANK, I also want to use English by default for this case.

[SpartaBI · Accepted solution]

Ok, something "quick and dirty" 😄: (US is probably the latest alphabetical country in your data):

Desired Result 2 = 
VAR _current_project = Users[Project]
VAR _tbl_1 = 
    FILTER(
        Users,
        Users[Project] = _current_project
    )
VAR _tbl_2 =
    GROUPBY( _tbl_1,Users[UsersCountry],"@Users", SUMX(CURRENTGROUP(), 1))
VAR _max_per_country = MAXX(_tbl_2, [@Users])
VAR _top_countries = 
    FILTER(
        _tbl_2,
        [@Users] = _max_per_country
    )
RETURN
    COALESCE(MAXX(_top_countries,Users[UsersCountry]), "US")

Hey, check out my showcase report:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543

If you like it, give it a thumbs up 🙂

[Sly]

I could use both of your solutions, but I think this one will do the trick !

Thank you very much, really appreciated !

[SpartaBI · Accepted solution]

For example, let's say in case of a tie, you want to return the country by sorting of it's name. 
So, is there will be a tie between CA and US, than the result will be CA:

Desired Result 2 = 
VAR _current_project = Users[Project]
VAR _tbl_1 = 
    FILTER(
        Users,
        Users[Project] = _current_project
    )
VAR _tbl_2 =
    GROUPBY( _tbl_1,Users[UsersCountry],"@Users", SUMX(CURRENTGROUP(), 1))
VAR _max_per_country = MAXX(_tbl_2, [@Users])
VAR _top_countries = 
    FILTER(
        _tbl_2,
        [@Users] = _max_per_country
    )
RETURN
    MINX(_top_countries,Users[UsersCountry])