Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rendalignacio
Helper I
Helper I

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.

 

Sample data.png

1 ACCEPTED SOLUTION

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.

1.PNG

Create a calculated column using the fomrula.

highest DFFAM = CALCULATE(MAX('Table'[Average]),ALLEXCEPT('Table','Table'[month]))

2.PNG

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

3.png

You can create a table visual to display the result as follows.

4.PNG

Best Regards,
Angelia


View solution in original post

18 REPLIES 18
CahabaData
Memorable Member
Memorable Member

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....

www.CahabaData.com

What i would actually want would be the highest average.

 

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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

 

Untitled.png

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

 

Actually, thats only the max... what i would want is to have the highest average per registry per month..

Hi,

 

If this is still not your expected result, then please share a screenshot of your expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

 

So Basically, in the dashboard, i want to show this.

 

So in the table, this will have the summary of which registry per month have the highest average DFFAM.

 

image.png

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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.Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.

 

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

 

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

 

Thank you very much

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.

1.PNG

Create a calculated column using the fomrula.

highest DFFAM = CALCULATE(MAX('Table'[Average]),ALLEXCEPT('Table','Table'[month]))

2.PNG

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

3.png

You can create a table visual to display the result as follows.

4.PNG

Best Regards,
Angelia


Hi,

 

Will try this later... This looks good.

 

Will update you later if it works on my end.

 

Thank you

Hi,

 

Replace the MAX with the AVERAGE function in the formula bar


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

 

Doing so would only get the average for all registry.

 

The goal is to have an average per registry per month....

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.