How to Calculate Percentage of a Value in Column

[susiechocolate]

Hello,

I have data has follows

subgroup	group	city
1a	T1	Miami
1b	T1	Toronto
1c	T1	Los Angeles
1d	T1	London
2a	Other	New York
2d	Other	Warsaw
3a	Other	Geneva
4b	Other	Beirut
5a	Other	Cairo
1d	T1	Beijing
2d	Other	Bangalore
3d	Other	New Delhi
1b	T1	Manila

I have another mapping table for regions

USA	Los Angeles
USA	New York
USA	Miami
Canada	Toronto
Middle East	Beirut
Middle East	Cairo
North Asia	Beijing
South Asia	New Delhi
South Asia	Bangalore
Southern Europe	Geneva
Southern Europe	London
Southeast Asia	Manila
Northern Europe	Warsaw

I want to make a table like the following

Group	Canada	USA	North Asia	South Asia	Northern Europe	Southern Europe	Southeast Asia	North Asia
Count T1	1	2	1	0	0	1	1	1
Count Other	0	1	0	2	1	1	0	0
Total	1	3	1	2	1	2	1	1
%T1	100%	66.7%	100%	0%	100%	50%	100%	100%

I am not sure of how to set this table up and get the counts and percentages of the T1 group. thank you

[ryan_mayu]

@susiechocolate 

you need to create the relationship between two tables and create measures

pls see the attachment below

[susiechocolate]

hi  @ryan_mayu 

thank you, I've followed your method but I need to make this in the opposite direction - the regions need to be the columns and the rest the calculations are the rows. please let me know how to reverse this thanks 

[ryan_mayu]

@susiechocolate 

here is a workaround for you. pls see attachment below

[susiechocolate]

Hi @ryan_mayu 

Thank you very much for this. Is there a way that the percentage can show as "[value]%" instead of a decimal place please?

[ryan_mayu · Accepted solution]

@susiechocolate 

you can try this

Measure = SWITCH(SELECTEDVALUE('Table (2)'[Column1]),"CountT1",'Table'[T1],"Count Other",'Table'[Other],"Total",'Table'[Total],"%T1",format('Table'[%T1],"0.0%"))

[susiechocolate]

hi @ryan_mayu 

Thank you very much this is exactly what I need!

[ryan_mayu]

you are welcome