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)
Boopep's avatar
Boopep
Icon for Helper I rankHelper 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)