Summarize and visualize distinct count on multiple columns

[Jgrohows]

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 #	Country	City
1	France	Paris
1	Germany	Berlin
1	Italy	Milan
1	Italy	Rome
1	Poland	Warsaw
1	Greece	Athens
2	France	Paris
2	Germany	Berlin
2	Germany	Munich
2	Greece	Athens
3	Germany	Berlin
3	Italy	Milan
3	Italy	Rome
3	Greece	Athens
4	Italy	Milan

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!

[onurbmiguel_ · Accepted solution]

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