Create a table that sums multiple columns according to a value

[Anonymous]

Hello everyone,

I'm pretty new to PowerBI and I've been looking at my issue in this forum, but I'm not sure to find the right case.

I'm trying to vizualize with a map the number of marketing activity that I run through regions:

Marketing activity	Regions
Activity 1	Bretagne, Corse, Normandie
Activity 2	Grand Est
Activity 3	Bretagne, Grand Est
Activity 4	Normandie, Bretagne

So the table I want to get is the following:

Region	# of activities
Bretagne	3
Corse	1
Normandie	2
Grand Est	2

What I've tried to do on my main table is to create a column per region that put a "1" when a region is mentionned:

Activity name	Regions	Bretagne	Corse	Normandie	Grand Est
Activity 1	Bretagne, Corse, Normandie	1	1	1	0
Activity 2	Grand Est	0	0	0	1
Activity 3	Bretagne, Grand Est	1	0	0	1
Activity 4	Normandie, Bretagne	1	0	1	0

I've used this formula for that:

Grand Est = IF(SEARCH("Grand Est",[Zone],1,0)>0,1,0)​

Now I'm trying to generate a new table with the summary of all the regions with the total of activities run, but I'm struggling to get the right formula for that

What I'm looking for is an equivalent of the "countif" function that checks what is the value in the first column and sums only if it matches the text. I've tried: 

Sum = calculate(sum('05 - Agences'[Grand Est])+sum('05 - Agences'[Bretagne])+sum('05 - Agences'[Normandie]),ALLEXCEPT(Region,Region[Région]))

But instead of getting "Bretagne" | 3, I get the sum of all the regions for all the lines.

Hope my explanations were clear enough, Enligsh isn't my native langage. Maybe my initial idea to create new columns with 1 and 0 was not the right way to do, I'm looking forward to get your feedbacks 🙂

Thanks a lot,

Mathieu

[gabrielreversi · Accepted solution]

Hello @Anonymous 

You might want to treat this structure in the power query before doing a DAX

1. make the division by the delimiter

2. Unpivot columns

3.  If you want, you can delete the "Attribute" columns

4.Now you can count with a simpler dax

I hope it helped you 🙂