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


CahabaData's avatar
CahabaData
Memorable Member
8 years ago

try this:

 

in a table visual; drag in your 3 key fields: Registry, MonthDate, DFFAM .... and then choose the DFFAM value to be MAX

 

although there is a little discrepancy in your post as you whether you want the highest or the highest average....both are stated..... so this maybe is not what you seek....

rendalignacio's avatar
rendalignacio
Helper I
8 years ago

What i would actually want would be the highest average.

 

Then maybe show the registry with the highest average per month.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Share the link from where we can download your workbook.  Also, show the expected result.

    • rendalignacio's avatar
      rendalignacio
      Helper I
      8 years ago

      Hi, 

       

      Sorry, i cannot give the access to my workbook.

       

      But what i would like is to get the highest DFFAM average per month per registry (here is a sample below).

       

      RegistrymonthDFFFAM
      311January0.1
      311January0.3
      311January0.4
      312January0.1
      312January0.2
      312January0.2
      313January0.5
      313January0.4
      313January0.3
      311February1.1
      311February1.1
      311February1.2
      312February1.3
      312February1.4
      312February1.5
      313February0.3
      313February0.5
      313February0.8
      311March0.9
      311March1.1
      311March0.5
      312March0.1
      312March1.2
      312March1.3
      313March1.7
      313March0.3
      313March0.5

       

      Also, in the dashboard, an indicator as shown below:

       

      MonthRegistryHighest average

       

      Hope you can help me with this.

       

      Thank you

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        I am still not clear of what you want, but here is the DAX formula i wrote for computing Maximum DFFAM

         

        = MAX([DFFFAM])

        Here's the result i got