Forum Discussion

rendalignacio's avatar
rendalignacio
Helper I
8 years ago
Solved

Getting the highest average per month

Hi there,   I am a newbie with power bi and having some problems on how I can show the highest average per month per cateogry.   Maybe you can help on this.   I want to get the highest DFFAM pe...
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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


Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

This is what i did.

 

Calculated field formula for computing Highest DFFAM

 

Highest DFFAM = MAXX(SUMMARIZE(Data,Data[Registry],"ABCD",average([DFFFAM])),[ABCD])

Calculated column formula for computing Average

 

=CALCULATE(AVERAGE([DFFFAM]),FILTER(Data,[Registry]=EARLIER([Registry])&&[month]=EARLIER([month])))

Calculated field formula for computing Registry for highest DFFAM

 

=LOOKUPVALUE([Registry],[Average],[Highest DFFAM])

Hope this helps.

 

 

rendalignacio's avatar
rendalignacio
Helper I
8 years ago

Hi,

 

Will try also this solution and will give a feedback regarding this.

 

Thank you very much