Countif for 50 country columns to show sum of each country

[Anonymous]

Hi, i'm a complete newbie to visualizations. I have 50 columns that I want do a countif on. In excel I can easily do this with a countif on all the columns (I already have a list of countries). I looked through a lot of solutions and unfortunately I don't seem to understand how to do this in Power BI. I am using Power BI Version 2.85.985.0 64-bit.

If someone can explain this to me like a 5 year old, I would really appreciate!

[jthomson]

Will be much, much easier if you clean the data up - unpivot all the columns so rather than having one row with 50 columns, you get 50 rows with two columns (one with the country, one with the value) - it's then a pretty simple countrows measure from there with whatever criteria you'd have been countifing on before

[Anonymous]

Hi jthomson, perhaps I didn't explain clear on my end. Here is what the data looks like (but for 50 columns):

each row is a business entity. The original data set had one column with each cell as all countries separated by a semi colon. I had to split that into multiple columns to get an accurate count of each country. 

I guess there are two things I need to do: get a unique list of all the countries that appear in all these columns, and get a count of them.

[Anonymous]

Perhaps there is another way to organize this data. I could import a second table with list of countries and count of each country; However, I don't see how I could connect it back to my original data set. Open to all suggestions!

[v-lionel-msft]

Hi @Anonymous ,

Is this not what you want?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Not really, because each column can have any country. Each row looks like this (starting with header):

entity_name; country1; country2; country3; country4

entity_x; Brazil; China; India; (blank); 

entity_y;(blank);(blank);(blank); (blank);

entity_z; Canada; Mexico; (blank); (blank);

each column can hold any country. The reason it goes to 50 columns is because a few entities do business with 50 countries. Some only do business with one country. A lot are just blank. Hope that helps!

[v-lionel-msft]

Hi @Anonymous ,

If you don't transform the table, you have to create 50 measures to count each column.
Or you can add all the columns to the table visual and use the aggregation feature that comes with the table visual.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.