Forum Discussion

Jgrohows's avatar
Jgrohows
Frequent Visitor
3 years ago
Solved

Summarize and visualize distinct count on multiple columns

Hello!

 

I am struggling to figure out a way to summarize and visualise an ask that involves determining distinct counts on multiple columns. Lets say i have a list of Employee #, Country and City and my ultimate goal is to identify how many employees traveled to 1 county, 2 counties, 3, 4 and onwards (for this example lets say there are 5 countries). 

 

Emplyee #CountryCity
1FranceParis
1GermanyBerlin
1ItalyMilan
1ItalyRome
1PolandWarsaw
1GreeceAthens
2FranceParis
2Germany Berlin
2Germany Munich
2GreeceAthens
3GermanyBerlin
3ItalyMilan
3ItalyRome
3GreeceAthens
4ItalyMilan

 

In the example table above, the result would be :

Traveled to 1 country - 1 (employee 4)

Traveld to 2 countries -  0 

Traveled to 3 countries - 2 (employee  2 & 3)

Traveled to 4 countries - 0 

Traveled to 5 countries - 1 (employee 1)

 

I then want to be able to create a visual that would look something like this

 

 

 

I am struggling to figure out the correct measures to develop in order to capturing this informaiton and visualze. Any help you have would be greatly appreciated.

 

Thank you!

Need Help
Tutorial Requests
  • onurbmiguel_'s avatar
    onurbmiguel_
    3 years ago

    hello Jgrohows

    Is this the output wanted ?

     

    I creat one column and one mesure: 

     

    No of countries = 
-- Column 
var _name = 'Table'[Name]

var _value = 
    CALCULATE(
        DISTINCTCOUNT('Table'[Country]),
        ALL('Table'),'Table'[Name] = _Name
    )

return 
_value

     

    Total of persons = 
-- Measure 
DISTINCTCOUNT('Table'[Name])

     

    check the solution in this LINK

     

    Best regards

    Bruno Costa | Solution Supplier

     

    Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍

    Take a look at the blog: PBI Portugal 

     

1 Reply

  • onurbmiguel_'s avatar
    onurbmiguel_
    Power Participant
    3 years ago

    hello Jgrohows

    Is this the output wanted ?

     

    I creat one column and one mesure: 

     

    No of countries = 
-- Column 
var _name = 'Table'[Name]

var _value = 
    CALCULATE(
        DISTINCTCOUNT('Table'[Country]),
        ALL('Table'),'Table'[Name] = _Name
    )

return 
_value

     

    Total of persons = 
-- Measure 
DISTINCTCOUNT('Table'[Name])

     

    check the solution in this LINK

     

    Best regards

    Bruno Costa | Solution Supplier

     

    Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍

    Take a look at the blog: PBI Portugal