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


rendalignacio's avatar
rendalignacio
Helper I
8 years ago

Hi,

 

I think this is what I would want then get the registry as well corresponding to the highest DFFAM.

 

Can you please show me the DAX code for this?

 

Thank you

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
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


  • rendalignacio's avatar
    rendalignacio
    Helper I
    8 years ago

    Hi,

     

    Will try this later... This looks good.

     

    Will update you later if it works on my end.

     

    Thank you