Getting the highest average per month

Hi rendalignacio,

I create the sample table you posted, name it as 'Test'.

First, create a summary table by clicking "New Table" under Modeling on Home page, type the following formula.

Table = SUMMARIZE(Test,Test[month],Test[Registry],"Average",AVERAGE(Test[DFFFAM]))

You will get the table below.



Create a calculated column using the fomrula.

highest DFFAM = CALCULATE(MAX('Table'[Average]),ALLEXCEPT('Table','Table'[month]))

Finally, create another new table to get expected table.

Result = SELECTCOLUMNS(FILTER('Table','Table'[Average]='Table'[highest DFFAM]),"Month",'Table'[month],"Registry",'Table'[Registry],"Highest average",'Table'[highest DFFAM

You can create a table visual to display the result as follows.



Best Regards,
Angelia