Count measure assistance in counting the total fields with data

[Boopep]

I want to count all the fields from SQD1-SQD0 to be used in the formula later.

SQD1	SQD2	SQD3	SQD4	SQD5	SQD6	SQD7	SQD8
4	4	4	4	4	4	4	4
4	4	4	4	4	4	4	4
4	4	4	4	4	4	4	4
4	4	4	0	0	4	4	4
4	4	0	5	4	5	4	5
4	4	4	4	4	4	4	4
4	4	4	4	4	4	4	4
0	0	0	0	0	0	0	0
4	4	0	4	0	4	5	4
4	4	4	4	4	4	4	4
4	4	4	4	0	4	4	5
4	4	4	4	5	4	4	4
4	4	4	4	4	4	4	4
4	4	4	4	0	4	4	4
4	4	4	4	0	4	4	4
4	4	4	4	0	4	4	4

I want to the total count to be 128. im using this formula

VAR TotalRespondents =

    CALCULATE(

        COUNTROWS(

            FILTER(

                'Sample',

                NOT ISBLANK('Sample'[SQD1]) ||

                NOT ISBLANK('Sample'[SQD2]) ||

                NOT ISBLANK('Sample'[SQD3]) ||

                NOT ISBLANK('Sample'[SQD4]) ||

                NOT ISBLANK('Sample'[SQD5]) ||

                NOT ISBLANK('Sample'[SQD6]) ||

                NOT ISBLANK('Sample'[SQD7]) ||

                NOT ISBLANK('Sample'[SQD8])

            )

        )

    )

Thanx in advnace

[Boopep]

This is my DAX Measure

The correct result of this shoud be 100% 

ANOTHER_SQD_1_8 =

VAR StronglyAgree =

    CALCULATE(

        COUNTROWS('Sample'),

        'Sample'[SQD1] = 5 &&

        'Sample'[SQD2] = 5 &&

        'Sample'[SQD3] = 5 &&

        'Sample'[SQD4] = 5 &&

        'Sample'[SQD5] = 5 &&

        'Sample'[SQD6] = 5 &&

        'Sample'[SQD7] = 5 &&

        'Sample'[SQD8] = 5

    )

VAR Agree =

    CALCULATE(

        COUNTROWS('Sample'),

        'Sample'[SQD1] = 4 &&

        'Sample'[SQD2] = 4 &&

        'Sample'[SQD3] = 4 &&

        'Sample'[SQD4] = 4 &&

        'Sample'[SQD5] = 4 &&

        'Sample'[SQD6] = 4 &&

        'Sample'[SQD7] = 4 &&

        'Sample'[SQD8] = 4

    )

VAR TotalRespondents =

    CALCULATE(

        COUNTROWS(

            FILTER(

                'Sample',

                NOT ISBLANK('Sample'[SQD1]) &&

                NOT ISBLANK('Sample'[SQD2]) &&

                NOT ISBLANK('Sample'[SQD3]) &&

                NOT ISBLANK('Sample'[SQD4]) &&

                NOT ISBLANK('Sample'[SQD5]) &&

                NOT ISBLANK('Sample'[SQD6]) &&

                NOT ISBLANK('Sample'[SQD7]) &&

                NOT ISBLANK('Sample'[SQD8])

            )

        )

    )

VAR NA_Count =

    CALCULATE(

        COUNTROWS('Sample'),

        'Sample'[SQD1] = 0 &&

        'Sample'[SQD2] = 0 &&

        'Sample'[SQD3] = 0 &&

        'Sample'[SQD4] = 0 &&

        'Sample'[SQD5] = 0 &&

        'Sample'[SQD6] = 0 &&

        'Sample'[SQD7] = 0 &&

        'Sample'[SQD8] = 0

    )

RETURN

(StronglyAgree + Agree) / ((TotalRespondents*8)- (NA_Count))

[v-achippa · Accepted solution]

Hi @Boopep,

Thank you for reaching out to Microsoft Fabric Community.

It looks like the issue here comes from using && to check if all 8 fields in a row are exactly 5 or 4, this condition is too restrictive and excludes valid cases where the row contains a mix of 4s and 5s.

To count the percentage of all responses across SQD1 to SQD8 use this below advanced measure to scan each row and each field:

ANOTHER_SQD_1_8_FIXED =
VAR AgreeOrStronglyAgreeCount =
SUMX (
'Sample',
VAR ValuesList = {
'Sample'[SQD1], 'Sample'[SQD2], 'Sample'[SQD3], 'Sample'[SQD4],
'Sample'[SQD5], 'Sample'[SQD6], 'Sample'[SQD7], 'Sample'[SQD8]
}
RETURN
COUNTAX (
FILTER (
ValuesList,
[Value] IN {4, 5}
),
1
)
)

VAR ValidResponseCount =
SUMX (
'Sample',
VAR ValuesList = {
'Sample'[SQD1], 'Sample'[SQD2], 'Sample'[SQD3], 'Sample'[SQD4],
'Sample'[SQD5], 'Sample'[SQD6], 'Sample'[SQD7], 'Sample'[SQD8]
}
RETURN
COUNTAX (
FILTER (
ValuesList,
[Value] <> 0
),
1
)
)

RETURN
DIVIDE(AgreeOrStronglyAgreeCount, ValidResponseCount)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Boopep,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Boopep,

We wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Boopep,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[hallenstal]

You should be able to unpivot the data in power query, go to transform data, choose to unpivot and then just use sum over the column.