topic Summarize of a summarize in DAX Commands and Tips

Summarize of a summarize

alfranco17 — Wed, 25 Nov 2020 18:54:13 GMT

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) )

Re: Summarize of a summarize

edhans — Wed, 25 Nov 2020 19:16:57 GMT

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.

Re: Summarize of a summarize

alfranco17 — Fri, 27 Nov 2020 02:10:07 GMT

Thank you!

Re: Summarize of a summarize

edhans — Fri, 27 Nov 2020 16:40:04 GMT

Glad to help alfranco17