Filtering is not giving giving the correct output

[Mourt]

Hello,

Im trying to filter the output based on role slicer added to table 1

for role 1 submission rate is submission counnt / total count

for role 2 the fixed count should always be 1 and total count is always 1 and if no submissions then 0 and submission rate = submission count / 1 

For role 3 ignore and show blank for submission values

I have tried the following however the total is summing up all percentages
Submission % = 
IF(SUM(Table1[Role])/ total submissions = 2, Divide(Fixed Submissions,1)),"")

I just wanted to filter between the roles and see the desired output, Thank you



Table 1

Group	annonymous_submission_id	Role_code
A	1	1
A	1	1
A	2	1
B	3	1
B	4	1
C	5	1
C	5	1
C	6	1
C	7	1
D	8	1
D	9	1
D	9	1
D	10	1
D	11	1
A	12	2
A	12	2
A	13	2
B	14	2
B	15	2
C	16	2
C	16	2
C	17	2
C	18	2
D	19	2
D	20	2
D	20	2
D	21	2
D	21	2
A	22	3
B	23	3
C	24	3
D	25	3

Table 2 

Group	User ID
A	113
A	242
A	114
B	122
B	274
B	216
B	126
B	256
C	267
C	279
D	297
D	133
D	229
E	842

[ryan_mayu]

@Mourt 

pls see the attachment below

[Mourt]

Hi @ryan_mayu  sorry for the late reply I was away for the last week..

Thank you for you reply however I was looking for a way to do this through slicers in one table not separate.. is it doable?

 Thank you and appreciated

[ryan_mayu]

@Mourt 

since you have duplicated values in both tables. It's better to create a dimension table,otherwise the relationship is many to many. Usually we don't recommend people to use m to m relationship.

[amitchandak]

@Mourt , You can try a measure like

Switch(True(),
not(isinscope(Table[Group])) , divide([submission count] ,[total count]), //grand total
max(Table[Role_code]) =1, divide([submission count] ,[total count]),
max(Table[Role_code]) =3, blank(),
// Add others
)

[Mourt]

Hi @amitchandak Thank you for your response
I have tried the DAX but it only works properly for role 1, Role 2, the submission rate should be (submission count / 1) and other roles still show percenatges 

Any advice?.. Thanks