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

With respect to the data that you have shared, please share how did you arrive at the numbers 0.6,0.7 and 0.8.  Please share the actual numbers that you want to see in that column with respect to the data that you shared earlier.

rendalignacio's avatar
rendalignacio
Helper I
8 years ago

Hi,

 

That is only a sample of something i want to get in power bi.

 

I cannot figure out on how i can get the Registry with the highest average per month.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Let's divide the solution into two parts.  First is to get the monthwise highest average DFFAM (see screenshot below).  Is this result correct.  If it is, then let me know and we will then get the Registry as well.

  • 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

  • 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