Summarize of a summarize

[alfranco17]

Hi.

I am struggling with this formula. A user can have several evaluations during a year. So I need to calculate the monthly average, and then average those monthly averages.

I am trying to summarize another summarize, but I can't get to the "MonthlyAverage" that contains the average for each month.

Any tips on how to fix it? They will be greatly appreciated.

Thanks.

Armando

SUMMARIZE(
    SUMMARIZE (
        'PositionEvaluationPeriod',
        Calendar[Date],        
        Users[idUser],
        "MonthlyAverage", AVERAGE ( Evaluation[Grade] )
    ),
    Users[idUser],
    "GeneralAverage", AVERAGE(MonthlyAverage)
)

[edhans]

Glad to help @alfranco17 

[alfranco17]

Thank you!

[edhans · Accepted solution]

Try this. 

Test =
SUMMARIZE(
    SUMMARIZE(
        'PositionEvaluationPeriod',
        Calendar[Date],
        Users[idUser],
        "MonthlyAverage", AVERAGE( Evaluation[Grade] )
    ),
    Users[idUser],
    "GeneralAverage", AVERAGE( [MonthlyAverage] )
)

The syntax is correct, but do not think it will work. You will probably need this:

Test =
VAR varFirstSummary =
    SUMMARIZE(
        'PositionEvaluationPeriod',
        Calendar[Date],
        Users[idUser],
        "MonthlyAverage", AVERAGE( Evaluation[Grade] )
    )
RETURN
    SUMMARIZE(
        varFirstSummary,
        Users[idUser],
        "GeneralAverage",
            AVERAGEX(
                varFirstSummary,
                [MonthlyAverage]
            )
    )

And you may still need to wrap AverageX in a CALCULATE, and you might still get wrong results. SUMMARIZE should generally only be used to create the summary fields. Use ADDCOLUMNS() to add the values.

Test =
VAR varFirstSummary =
    ADDCOLUMNS(
        SUMMARIZE(
            'PositionEvaluationPeriod',
            Calendar[Date],
            Users[idUser]
        ),
        "MonthlyAverage",
            CALCULATE(
                AVERAGE( Evaluation[Grade] )
            )
    )
RETURN
    ADDCOLUMNS(
        SUMMARIZE(
            varFirstSummary,
            Users[idUser]
        ),
        "GeneralAverage",
            CALCULATE(
                AVERAGEX(
                    varFirstSummary,
                    [MonthlyAverage]
                )
            )
    )

See Best Practices Using SUMMARIZE and ADDCOLUMNS - SQLBI for more info on this. SUMMARIZE() is a bit buggy in some cases is the reason for this.