cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Create a table that sums multiple columns according to a value

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

1 ACCEPTED SOLUTION
Frequent Visitor

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 🙂

Frequent Visitor

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 🙂

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors