Sum multiple records based on a value on only one of those records

[chrsko47]

I have a table with scores for QA evaluations.

• Each evaluation has multiple scored questions
• The score for each question is stored in a separate record (1 evaluation, 20 records)
• Each record has the same evaluation key that identifies the evaluation
• One of the records determines if the score should count or not

I need to sum the scores where the 1 (of 20) record indicates it should be counted.

The below measure works, bringing back individual scores. But it does not work if I try to sum multiple scores because it brings back the score for the max record Id instead of a sum of all records. 

The first half of the measure idendifies the ID that should be counted, the second part sums them. Using Max in the calculate is the problem, but I cannot figure out a way of doing this without using max or min.

QA Score =

// Identify scoreable Key

var QAKey =

CALCULATE(max(TMM_EE_QA[EVALUATION_KEY]),TMM_EE_QA[QUESTION_TEXT] = "Evaluation Type", TMM_EE_QA[ANSWER_TEXT] = "[Performance Evaluation]")

VAR QASc =

CALCULATE(SUM(TMM_EE_QA[MAX_QUESTION_SCORE]),TMM_EE_QA[EVALUATION_KEY] = QAKey)

return QASc

[Anonymous]

Hi @chrsko47,

Thanks for reaching out to the Microsoft fabric community forum.

You're right that using MAX limits the logic to a single evaluation, which is why you're only getting the score from one evaluation instead of summing across all qualifying ones.

To get the total score across all evaluations where the "Evaluation Type" question is answered as "[Performance Evaluation]", we need to first identify all the EVALUATION_KEYs that meet this condition. Then, we can sum the scores for all records associated with those evaluations.

Try this DAX:

QA Score =
VAR QualifiedEvaluations =
FILTER (
VALUES ( TMM_EE_QA[EVALUATION_KEY] ),
CALCULATE (
COUNTROWS ( TMM_EE_QA ),
TMM_EE_QA[QUESTION_TEXT] = "Evaluation Type",
TMM_EE_QA[ANSWER_TEXT] = "[Performance Evaluation]"
) > 0
)

RETURN
CALCULATE (
SUM ( TMM_EE_QA[MAX_QUESTION_SCORE] ),
TMM_EE_QA[EVALUATION_KEY] IN QualifiedEvaluations
)

Here the QualifiedEvaluations variable builds a list of all evaluation keys where there's a record with "Evaluation Type" = "[Performance Evaluation]". Then, we use that list to filter the full dataset and sum the scores for all records tied to those evaluations.

This should give you the correct total score across all applicable evaluations.

I would also take a moment to thank @techies, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[techies · Accepted solution]

Hi @chrsko47 please check this measure

Total QA Score =

VAR ValidEvaluations =

    FILTER (

        VALUES ( TMM_EE_QA[EVALUATION_KEY] ),

        CALCULATE (

            COUNTROWS ( TMM_EE_QA ),

            TMM_EE_QA[QUESTION_TEXT] = "Evaluation Type",

            TMM_EE_QA[ANSWER_TEXT] = "[Performance Evaluation]"

        ) > 0

    )

RETURN

CALCULATE (

    SUM ( TMM_EE_QA[MAX_QUESTION_SCORE] ),

    TMM_EE_QA[EVALUATION_KEY] IN ValidEvaluations,

    TMM_EE_QA[QUESTION_TEXT] <> "Evaluation Type"  

)

[techies]

Hi @chrsko47 could you pls share what your sample data looks like?