I want to get a result based on the survey result.Using this formula:Number of 'Strongly Agree'(5) + Number of 'Agree' (4)Divided byNumber of Respondents - Number of 'N/A' (0) 1. I want to get the result of SQD02. I want to get the result of SQD 1-83. I want to get the result of per office of SQD0 and SQD 1-8 OFFICESTART_SURVEY_DATESTART_DATEEND_DATECC1CC2CC3SQD0SQD1SQD2SQD3SQD4SQD5SQD6SQD7SQD8Norte LHIO07/31/202407/31/202407/31/2024443444444444Norte LHIO05/20/202403/06/202403/06/2024311444444444Norte LHIO03/08/202402/05/202402/05/2024311444444444Norte LHIO11/07/202407/11/202407/11/2024322444400444Albay LHIO05/08/202403/06/202403/06/2024454414012141Albay LHIO07/01/202402/07/202402/07/2024111444444444Albay LHIO08/09/202409/08/202409/08/2024222444444444Albay LHIO10/10/202410/10/202410/10/2024443000000000Albay LHIO10/28/202410/28/202410/28/2024443414040411Albay LHIO11/14/202411/14/202411/14/2024222444444444Albay LHIO12/03/202403/12/202403/12/2024331444440411Camarines 06/21/202406/21/202406/21/2024454114411444Camarines 02/12/202502/19/202502/19/2025111444444444Camarines 12/19/202412/19/202412/19/2024443444440444Camarines 07/25/202407/25/202407/25/2024111444440444Camarines 07/31/202407/31/202407/31/2024111444440444 Help is very much appreciated.Thank you in advance

After reading and trial and error i think i got it correctSelf 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)

Boopep

Helper I

1 year ago

Solved

Request for Assistance in computing the formula

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

General Comment

Tips and Tricks

Boopep
1 year ago
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)

3 Replies

bhanu_gautam
Super User
1 year ago
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
  Helper I
  1 year ago
  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
Boopep
Helper I
1 year ago
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)

Forum Discussion

Request for Assistance in computing the formula

3 Replies

Related content

Request for Assistance with Microsoft Fabric Login Issue

Request for more compute capacity in MS Fabric trial account

Request for Assistance with Power BI DAX Logic

DAX assistance needed

Assistance with converting CURL request into PowerQuery