Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
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 activityRegions
Activity 1Bretagne, Corse, Normandie
Activity 2Grand Est
Activity 3Bretagne, Grand Est
Activity 4Normandie, Bretagne

 

So the table I want to get is the following:

Region# of activities
Bretagne3
Corse1
Normandie2
Grand Est2

 

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 nameRegionsBretagneCorseNormandieGrand Est
Activity 1Bretagne, Corse, Normandie1110
Activity 2Grand Est0001
Activity 3Bretagne, Grand Est1001
Activity 4Normandie, Bretagne101

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
gabrielreversi
Frequent Visitor

Hello @Anonymous 

 

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

 

gabrielreversi_0-1617969711492.png

1. make the division by the delimiter

 

gabrielreversi_2-1617969868458.png

 

gabrielreversi_3-1617969901050.png

gabrielreversi_1-1617969801237.png

 

 

2. Unpivot columns

 

gabrielreversi_4-1617970048065.png

 

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

gabrielreversi_6-1617970194322.png

 

4.Now you can count with a simpler dax

 

gabrielreversi_7-1617970232282.png

gabrielreversi_8-1617970389913.png

I hope it helped you 🙂

View solution in original post

1 REPLY 1
gabrielreversi
Frequent Visitor

Hello @Anonymous 

 

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

 

gabrielreversi_0-1617969711492.png

1. make the division by the delimiter

 

gabrielreversi_2-1617969868458.png

 

gabrielreversi_3-1617969901050.png

gabrielreversi_1-1617969801237.png

 

 

2. Unpivot columns

 

gabrielreversi_4-1617970048065.png

 

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

gabrielreversi_6-1617970194322.png

 

4.Now you can count with a simpler dax

 

gabrielreversi_7-1617970232282.png

gabrielreversi_8-1617970389913.png

I hope it helped you 🙂

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors