cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sly
New Member

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

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
3 ACCEPTED SOLUTIONS
SpartaBI
Community Champion
Community Champion

@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 🙂

View solution in original post

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

View solution in original post

SpartaBI
Community Champion
Community Champion

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 🙂

View solution in original post

7 REPLIES 7
SpartaBI
Community Champion
Community Champion

@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 🙂

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
Community Champion
Community Champion

@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

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
Community Champion
Community Champion

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 🙂

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

Thank you very much, really appreciated !

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.