Request for Assistance in computing the formula

[Boopep]

I want  to get a result based on the survey result.

Using this formula:

Number of 'Strongly Agree'(5) + Number of 'Agree' (4)

Divided by

Number of Respondents - Number of 'N/A' (0)

1. I want to get the result of SQD0

2. I want to get the result of SQD 1-8

3. I want to get the result of per office of SQD0 and SQD 1-8

OFFICE	START_SURVEY_DATE	START_DATE	END_DATE	CC1	CC2	CC3	SQD0	SQD1	SQD2	SQD3	SQD4	SQD5	SQD6	SQD7	SQD8
Norte LHIO	07/31/2024	07/31/2024	07/31/2024	4	4	3	4	4	4	4	4	4	4	4	4
Norte LHIO	05/20/2024	03/06/2024	03/06/2024	3	1	1	4	4	4	4	4	4	4	4	4
Norte LHIO	03/08/2024	02/05/2024	02/05/2024	3	1	1	4	4	4	4	4	4	4	4	4
Norte LHIO	11/07/2024	07/11/2024	07/11/2024	3	2	2	4	4	4	4	0	0	4	4	4
Albay LHIO	05/08/2024	03/06/2024	03/06/2024	4	5	4	4	1	4	0	1	2	1	4	1
Albay LHIO	07/01/2024	02/07/2024	02/07/2024	1	1	1	4	4	4	4	4	4	4	4	4
Albay LHIO	08/09/2024	09/08/2024	09/08/2024	2	2	2	4	4	4	4	4	4	4	4	4
Albay LHIO	10/10/2024	10/10/2024	10/10/2024	4	4	3	0	0	0	0	0	0	0	0	0
Albay LHIO	10/28/2024	10/28/2024	10/28/2024	4	4	3	4	1	4	0	4	0	4	1	1
Albay LHIO	11/14/2024	11/14/2024	11/14/2024	2	2	2	4	4	4	4	4	4	4	4	4
Albay LHIO	12/03/2024	03/12/2024	03/12/2024	3	3	1	4	4	4	4	4	0	4	1	1
Camarines	06/21/2024	06/21/2024	06/21/2024	4	5	4	1	1	4	4	1	1	4	4	4
Camarines	02/12/2025	02/19/2025	02/19/2025	1	1	1	4	4	4	4	4	4	4	4	4
Camarines	12/19/2024	12/19/2024	12/19/2024	4	4	3	4	4	4	4	4	0	4	4	4
Camarines	07/25/2024	07/25/2024	07/25/2024	1	1	1	4	4	4	4	4	0	4	4	4
Camarines	07/31/2024	07/31/2024	07/31/2024	1	1	1	4	4	4	4	4	0	4	4	4

Help is very much appreciated.

Thank you in advance

[Boopep · Accepted solution]

After reading and trial and error i think i got it correct

Self pat on the back!

Measure SQD1-8 =

VAR Very_Satisfactory =

SUMX(

    'Sample',

    IF('Sample'[SQD1] = 5, 1, 0) +

    IF('Sample'[SQD2] = 5, 1, 0) +

    IF('Sample'[SQD3] = 5, 1, 0) +

    IF('Sample'[SQD4] = 5, 1, 0) +

    IF('Sample'[SQD5] = 5, 1, 0) +

    IF('Sample'[SQD6] = 5, 1, 0) +

    IF('Sample'[SQD7] = 5, 1, 0) +

    IF('Sample'[SQD8] = 5, 1, 0)

)

VAR Satisfactory =

SUMX(

    'Sample',

    IF('Sample'[SQD1] = 4, 1, 0) +

    IF('Sample'[SQD2] = 4, 1, 0) +

    IF('Sample'[SQD3] = 4, 1, 0) +

    IF('Sample'[SQD4] = 4, 1, 0) +

    IF('Sample'[SQD5] = 4, 1, 0) +

    IF('Sample'[SQD6] = 4, 1, 0) +

    IF('Sample'[SQD7] = 4, 1, 0) +

    IF('Sample'[SQD8] = 4, 1, 0)

)

VAR NA_Count =

    SUMX(

    'Sample',

    IF('Sample'[SQD1] = 0, 1, 0) +

    IF('Sample'[SQD2] = 0, 1, 0) +

    IF('Sample'[SQD3] = 0, 1, 0) +

    IF('Sample'[SQD4] = 0, 1, 0) +

    IF('Sample'[SQD5] = 0, 1, 0) +

    IF('Sample'[SQD6] = 0, 1, 0) +

    IF('Sample'[SQD7] = 0, 1, 0) +

    IF('Sample'[SQD8] = 0, 1, 0)

)

VAR TotalRespondents = COUNTROWS('Sample')  

RETURN

(Very_Satisfactory + Satisfactory) / ((TotalRespondents*8)- NA_Count)

[bhanu_gautam]

@Boopep , Create measures for this

Measure for SQD0:

SQD0_Result =
VAR StronglyAgree = CALCULATE(COUNTROWS('Table'), 'Table'[SQD0] = 5)
VAR Agree = CALCULATE(COUNTROWS('Table'), 'Table'[SQD0] = 4)
VAR TotalRespondents = COUNTROWS('Table')
VAR NA_Count = CALCULATE(COUNTROWS('Table'), 'Table'[SQD0] = 0)
RETURN
(StronglyAgree + Agree) / (TotalRespondents - NA_Count)

Measure for SQD1-8:

SQD1_8_Result =
VAR StronglyAgree = CALCULATE(COUNTROWS('Table'), 'Table'[SQD1] = 5 || 'Table'[SQD2] = 5 || 'Table'[SQD3] = 5 || 'Table'[SQD4] = 5 || 'Table'[SQD5] = 5 || 'Table'[SQD6] = 5 || 'Table'[SQD7] = 5 || 'Table'[SQD8] = 5)
VAR Agree = CALCULATE(COUNTROWS('Table'), 'Table'[SQD1] = 4 || 'Table'[SQD2] = 4 || 'Table'[SQD3] = 4 || 'Table'[SQD4] = 4 || 'Table'[SQD5] = 4 || 'Table'[SQD6] = 4 || 'Table'[SQD7] = 4 || 'Table'[SQD8] = 4)
VAR TotalRespondents = COUNTROWS('Table')
VAR NA_Count = CALCULATE(COUNTROWS('Table'), 'Table'[SQD1] = 0 || 'Table'[SQD2] = 0 || 'Table'[SQD3] = 0 || 'Table'[SQD4] = 0 || 'Table'[SQD5] = 0 || 'Table'[SQD6] = 0 || 'Table'[SQD7] = 0 || 'Table'[SQD8] = 0)
RETURN
(StronglyAgree + Agree) / (TotalRespondents - NA_Count)

Measure for SQD0 and SQD1-8 per Office:

DAX
SQD0_Per_Office =
VAR StronglyAgree = CALCULATE(COUNTROWS('Table'), 'Table'[SQD0] = 5, ALLEXCEPT('Table', 'Table'[OFFICE]))
VAR Agree = CALCULATE(COUNTROWS('Table'), 'Table'[SQD0] = 4, ALLEXCEPT('Table', 'Table'[OFFICE]))
VAR TotalRespondents = CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[OFFICE]))
VAR NA_Count = CALCULATE(COUNTROWS('Table'), 'Table'[SQD0] = 0, ALLEXCEPT('Table', 'Table'[OFFICE]))
RETURN
(StronglyAgree + Agree) / (TotalRespondents - NA_Count)

DAX
SQD1_8_Per_Office =
VAR StronglyAgree = CALCULATE(COUNTROWS('Table'), ('Table'[SQD1] = 5 || 'Table'[SQD2] = 5 || 'Table'[SQD3] = 5 || 'Table'[SQD4] = 5 || 'Table'[SQD5] = 5 || 'Table'[SQD6] = 5 || 'Table'[SQD7] = 5 || 'Table'[SQD8] = 5), ALLEXCEPT('Table', 'Table'[OFFICE]))
VAR Agree = CALCULATE(COUNTROWS('Table'), ('Table'[SQD1] = 4 || 'Table'[SQD2] = 4 || 'Table'[SQD3] = 4 || 'Table'[SQD4] = 4 || 'Table'[SQD5] = 4 || 'Table'[SQD6] = 4 || 'Table'[SQD7] = 4 || 'Table'[SQD8] = 4), ALLEXCEPT('Table', 'Table'[OFFICE]))
VAR TotalRespondents = CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[OFFICE]))
VAR NA_Count = CALCULATE(COUNTROWS('Table'), ('Table'[SQD1] = 0 || 'Table'[SQD2] = 0 || 'Table'[SQD3] = 0 || 'Table'[SQD4] = 0 || 'Table'[SQD5] = 0 || 'Table'[SQD6] = 0 || 'Table'[SQD7] = 0 || 'Table'[SQD8] = 0), ALLEXCEPT('Table', 'Table'[OFFICE]))
RETURN
(StronglyAgree + Agree) / (TotalRespondents - NA_Count)

[Boopep]

In Measure for SQD1-8: 

how do i make that all the fields in SQD1-8 are being counted not just the rows

110 should be the total respondent for this query