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
PBIn00b
Advocate I
Advocate I

AVG of of MAX Values

Hello everyone, I am hoping someone can help me here.

 

In my rows of data I have a column called sessionID.  This sessionID can be duplicated several times on different rows.  However, when I use the sessionID as a distinct value it can give me the exact number of sessions.

 

Within each row I also have a value that would be something like "Backup Size" and these values can be different within each row.  This means the same sessionID (showing in multiple rows) can show different values in "Backup Size"

 

What I would like to do is find the MAX Backup Size within a group of duplicate sessionIDs and then Average that over all unique sessionIDs.

 

sessionID 123456 Backup 0

sessionID 123456 Backup 2

sessionID 123456 Backup 1

 

This would return 1 session with a max backup of 2

 

Sorry this seems so complex. Please let me know if I can do anything to make it more clear.

 

 

 

 

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

hi @PBIn00b

 

Use this measure and Try it

 

MaxBackupSize-Measure = AVERAGEX(SUMMARIZE('Backup-Sessions';'Backup-Sessions'[SessionID];"MaxBackupSize";Max('Backup-Sessions'[Backup]));[MaxBackupSize])

 

Let me know is this help you




Lima - Peru

View solution in original post

7 REPLIES 7
Vvelarde
Community Champion
Community Champion

hi @PBIn00b

 

Use this measure and Try it

 

MaxBackupSize-Measure = AVERAGEX(SUMMARIZE('Backup-Sessions';'Backup-Sessions'[SessionID];"MaxBackupSize";Max('Backup-Sessions'[Backup]));[MaxBackupSize])

 

Let me know is this help you




Lima - Peru

Ok, I just checked some numbers and it appears accurate.  I may want to change my filter from SessionID though because users will have multiple session IDs. I will find something more unique to each user.

 

Thanks a ton for your help!

 

I have to say, so far the PowerBI community has been pretty top notch.  I have seen very few "hey, someone else asked that question so I am going to ignore you" posts.

Hi @Vvelarde,

 

 

I think that may have worked!  The average is lower than I thought it would be, but I will check the numbers.

 

Can you give me a basic breakdown of what is happening in the formula?

Vvelarde
Community Champion
Community Champion

@PBIn00b

 

The formula act in this way:

 

First Summarize the sessions and extract the Max Value in BackupSize Table.

 

Example:

 

Session 1    2

Session 1   3

Session 2   1

Session 2   4

 

The result of summarize is

 

Session 1    3

Session 2   4

 

Next with AverageX take the 2 results (3 y 4) and calculate the Average. In this example : 3.5

 




Lima - Peru

i had same issue... you explained very well! thank you!

I am very much a newbie to DAX and doing more complex PowerBI pieces.  I appreciate your time on this.

Vvelarde
Community Champion
Community Champion

Am newbie too; I discover DAX 2 months ago; before i never used. This forum is very good to read and learn all the expert's publications.




Lima - Peru

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.