Average of values using MAXX

DAX DAX Calculations
1

Hello everyone,

I was hoping someone might be able to help with what I think should be quite simple but I can’t get to work.

I have a table with test results. Each test has 5 parts called the CAT4 type (overall, verbal, non-verbal, quantitative and spatial). Pupils sit the test in different years so we can see changes over time. This is shown in the test cycle column.

I’m trying to create a measure that calculates the average score based on only the most recent test a pupil has sat. The logic goes something like:

For each StudentID, filter the table to only show the max of test cycle, then average the CAT4 score.

Any help much appreciated.

Many thanks
Matt

image
image742×544 12.1 KB

2

Hi @corkemp,

See if this helps you solve it.

Max Test Cycle = 
    MAXX( ALL( Scores[Cycle] ), Scores[Cycle] )

and

Avg Last Cycle = 
    CALCULATE(
        AVERAGEX(
            FILTER( Scores,
                Scores[Cycle] = [Max Test Cycle]
            ),  Scores[Score]
        ), ALLEXCEPT( Scores, Scores[StudentID] )
    )

If you need further assistance please provide a sample PBIX file.

3

@corkemp
Is there a specific requirement to use iterating function MAXX?
The same result can also be achieved with aggregating functions and if your dataset is huge, this will help in improving the performance. Nonetheless, both methods will work. The measure with the aggregating function is as follows:

New Measure =
VAR MaxTestCycle = MAX( Sheet1 [Test cycle] ) 
VAR Result = CALCULATE(
    AVERAGE( Sheet1 [CAT4Score] ),
    FILTER(
        Sheet1,
        Sheet1 [Test cycle] = MaxTestCycle
    ),
    ALLEXCEPT( Sheet1, Sheet1 [StudenID] )
)

RETURN Result

I have attached both the dataset file and pbix file showing both the methods.

If you include the dataset and pbix file when you ask question on the forum, it will really help in answering the query speedily and accurately.

Thanks.Data.xlsx (8.6 KB) PBIX.pbix (26.2 KB)

4

Thank you both. There isn’t a specific requirement to use MAXX - I just assumed that would be a part of the solution. I’ll give the solutions a go shortly.

Many thanks
Matt

5

Hi @corkemp, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

6

Hi @corkemp,

No there isn’t. Like @MudassirAli illustrated the same can be achieved with the aggregation MAX just know that this is syntax sugar, under the hood the DAX engine uses the iterator MAXX