Forum Discussion

Boopep's avatar
Boopep
Icon for Helper I rankHelper 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 ...
General Comment
Tips and Tricks
  • Boopep's avatar
    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)
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper 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's avatar
Boopep
Icon for Helper I rankHelper 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