DAX for calculating totals from more that one column

[Ngars]

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

[vivran22]

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

[Ngars]

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. 

[Ngars]

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

[vivran22]

@Ngars 

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