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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## DAX for calculating totals from more that one column

Hi,

Im trying to create a measure that calculates a total from multiple columns. For example.

 # Name Type 1 Type 2 1 Bulbasaur Grass Poison 2 Ivysaur Grass Poison 3 Venusaur Grass Poison 4 Charmander Fire 5 Charmeleon Fire 6 Charizard Fire Flying 7 Squirtle Water 8 Wartortle Water 9 Blastoise Water 23 Ekans Poison 24 Arbok Poison 16 Pidgey Normal Flying

Im trying to calculate the total from Type 1 and Type 2.  End result will look something like this

 Type Total Grass 3 Fire 3 Water 3 Poison 5 Flying 2 Normal 1

Im fairly new to DAX so any help is much appreciated

Thanks,

Sam

4 REPLIES 4
Community Champion

Hey @Ngars ,

You may try the UNPIVOT option in Power Query to transform your table:

It will bring all your data in one column:

Hope this helps.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

Helper I

Hi @vivran22 ,

Heres is a more accurate table for what im trying to achieve

 Employer Industry Sector Industry Sector 2 Industry Sector 3 Location: town / city Location: TPK Region Employer size category Number of Cadets contracted Date approved by ISC Contract execution date Interim monitoring Report received Final monitoring report received Financial year Āhau NZ Limited Information media and telecommunications Te Tai Tokerau 1 10/03/2020 26/08/2020 5/10/2020 2019/20 All Area Scaffolding Limited Construction Otaki Te Tai Hauāuru 3 17/06/2019 29/11/2019 2018/19 Broadspectrum (New Zealand) Limited Electricity, gas, water and waste services Construction Auckland National / multiple 50+ 15 30/07/2020 2020/21 Alliance Services Limited Construction Tāmaki Makaurau 2 9/02/2020 20/08/2020 2019/20

So im trying to join the three industry columns to show totals i.e the contruction total would be 3.

However, I have visuals and measure that SUM other columns e.g. "Number of Cadets Contracted".

By using the UNPOVIT option it would create duplicated rows of each, which would then double the totals of my other measure and visuals.

Sorry I cant seem too attach a pibix file.

Helper I

Hey @vivran22,

I did try and unpivot the columns, the only issue I had was it created duplicate rows, and it creates problems for other visuals and totals I have.

Thanks,

Sam

Community Champion

Can you share more details about the model schema, may be the sample pbix file?

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors