Forum Discussion

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 per month per aircraft.

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

18 Replies

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
  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
    Super User
    8 years ago
    Hi,
    
    Share the link from where we can download your workbook. Also, show the expected result.

Related content