Calculate percentage of categorical variables that is grouped by another categorical varibable

[Anonymous]

Hi,

Essentially I would like to be able to get the percentage of a unique variable in a column (Type) over a group defined by another column (Level 1 or Level 2). Much like the percentage that is displayed by a pie chart. 

This is my hypothetical table, Level 1 and Level 2 are the categorical variables in which I would like to group by, and the percentage should be calculated based on the Type column. 

The rest of the columns are all values that should be calculated using DAX, but I ran into many difficulties trying to get those figures. Would really appreciate some help here. 

Ultimately, I would like to dynamically create a column that outputs the percentage of 'Type' depending on what the users selects on:

1. Which type he would like to compute the percentage on, for example, 'Malaysia', or 'India'

2. The level of hiarachy that is selected 

Thanks in advance! 

Level 1	Level 2	Type	Number of Malaysia in Level 1 Group	Total number of Type in Level 1	Proportion of Malaysia in Level 1	Number of Malaysia in Level 2 Group	Total number of Type in Level 1	Proportion of Malaysia in Level 2
A	A	China	2	4	50.00%	0	2	0.00%
A	A	China	2	4	50.00%	0	2	0.00%
A	B	Malaysia	2	4	50.00%	2	2	100.00%
A	B	Malaysia	2	4	50.00%	2	2	100.00%
B	C	India	1	4	25.00%	0	1	0.00%
B	D	China	1	4	25.00%	0	2	0.00%
B	D	China	1	4	25.00%	0	2	0.00%
B	E	Malaysia	1	4	25.00%	1	2	50.00%
C	E	Taiwan	0	4	0.00%	0	2	0.00%
C	F	Taiwan	0	4	0.00%	0	2	0.00%
C	F	Norway	0	4	0.00%	0	2	0.00%
C	G	Norway	0	4	0.00%	0	1	0.00%

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a new table without any relationship to other tables,

filter table = VALUES(Sheet1[Type])

Add Type column from "filter table" to the slicer,

Add measures in Sheet1,

number selected = CALCULATE( COUNT(Sheet1[Type]),FILTER(Sheet1,Sheet1[Type]=SELECTEDVALUE('filter table'[Type])))

total number = COUNT(Sheet1[Type])

Proportion% = [number selected]/[total number]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous ,

Can you upload sample data, output expected, pbix to Google / One Drive and share the link here.

From the sample data set is it that you always calculate % with respect to Malaysia only or does it change also.

Cheers

CheenuSing

[Anonymous]

Hi @Anonymous,

The PBIx file is here : https://mega.nz/#!HApW0KYS!-OqOsLaRfT_rlgKUhTjq6gJvG0v3u4k02f8hP_UcLT8

1. The first three columns are the raw data 
2. The rest of the columns are the expected ouput 
3. I am interested in one column only, the proportion/percentage of the selected Type over a selected Level, e.g. 'Level 1', or 'Level 2'. Column F if type = Malaysia and Level = Level 1, Column I if type = Malaysia and Level = Level 2
4. Yes, I would like the user to be able to select a different Type, e.g. 'India','China', and also select the level of hierarchy, e.g. 'Level 1','Level 2'

[Anonymous]

hi @Anonymous 

Please upload to Google or One Drive and share the link.  The link you provided takes me to a suspicious site.

Cheers

CheenuSing

[Anonymous]

@Anonymous,

Sure, my Google Drive quota has reached that's why. 

Link to PBIx