Count with multiple conditions

[AishwaryaP_]

Q1_A	Q1_B	Q1_C	Q1_D	State
4	5	2	2	1
2	4	1	1	2
3	2	2	5	2
3	5	3	3	1
3	5	2	4	1
2	1	5	2	1
3	1	4	5	1
1	2	1	5	2
2	3	5	3	1

I want to calculate count of 4 from Q1_A, Q1_B, Q1_C, Q1_D for State= 1 and want to calculate % of Count of 4/count State =1 

The answer should be,

Count of 4 for State 1= 3, 

Total count of state 1 = 6 , % = 50% 

Need help in writing a measure. 

[Vera_33]

Hi @AishwaryaP_ 

If you don't want to change the table format, then try this one

count = 
VAR T1=FILTER('Table','Table'[State]=1)
RETURN
COUNTROWS(FILTER(T1, 4 IN {[Q1_A],[Q1_B],[Q1_C],[Q1_D]}))

count% = 
VAR T1=FILTER('Table','Table'[State]=1)
RETURN
[count]/COUNTROWS(T1)

[ryan_mayu · Accepted solution]

@AishwaryaP_ 

add an index colomn and select index and state column to unpivot other columns.

Then create a measure

measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[State]=1 && 'Table'[Value]=4))
/CALCULATE(DISTINCTCOUNT('Table'[Index]),FILTER('Table','Table'[State]=1))

pls see the attachment below