cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Calculate the average of values across several columns in same row?

For each observation (row), I am trying to add a calculated column that displays the average across seven columns and I am stuck. I guess what I am looking for is a "row average" that will ignore null values. Here's the structure:

 

Capture.PNG

 

 

 

 

 

 

 

 

For the first observation, PCODE 8411, the average of these seven columns, ignoring null values, is [(1+4+1+1)/4] = 1.75.

 

There are approximately 30 columns, so I only want to average these seven.

 

Any help would be great!

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate V
Advocate V

Re: Calculate the average of values across several columns in same row?

@Rymatt830

You could do two calculated columns, one for total between all 7 columns, and one that calculates the number of non blank columns.

Avg Across columns.png

Total Across Columns:=([a]+[b]+[c]+[d]+[e]+[f]+[g])

 

Non Blank Columns =IF(ISBLANK([a]),0,1)+IF(ISBLANK([b]),0,1)+IF(ISBLANK([c]),0,1)+IF(ISBLANK([d]),0,1)+IF(ISBLANK([e]),0,1)+IF(ISBLANK([f]),0,1)+IF(ISBLANK([g]),0,1)

 

Avg=[Total across columns]/[Non Blank Columns]

 

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. 

 

Thanks,

Ryan Durkin

 

View solution in original post

10 REPLIES 10
Highlighted
Advocate V
Advocate V

Re: Calculate the average of values across several columns in same row?

@Rymatt830

You could do two calculated columns, one for total between all 7 columns, and one that calculates the number of non blank columns.

Avg Across columns.png

Total Across Columns:=([a]+[b]+[c]+[d]+[e]+[f]+[g])

 

Non Blank Columns =IF(ISBLANK([a]),0,1)+IF(ISBLANK([b]),0,1)+IF(ISBLANK([c]),0,1)+IF(ISBLANK([d]),0,1)+IF(ISBLANK([e]),0,1)+IF(ISBLANK([f]),0,1)+IF(ISBLANK([g]),0,1)

 

Avg=[Total across columns]/[Non Blank Columns]

 

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. 

 

Thanks,

Ryan Durkin

 

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Calculate the average of values across several columns in same row?

@Rymatt830 easy way to do is go to query editor after importing your data, make sure data type of seven columns is whole number.

 

Capture.PNG

 

 

 

 

Then select all seven columns, under Transform tab click Unpivot Columns. This will give you table as below.

 

Capture.PNG

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

Then under Transform tab click Group By and fill details as below and click OK.

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Capture.PNG

 

 

 

 

 

 

Close&Apply and everytime you refresh you will get these steps applied.

 

 

 

Highlighted
Helper I
Helper I

Re: Calculate the average of values across several columns in same row?

Thank you for the suggestion, and this solution would be ideal if I didn't have 30 additional columns in my query. Also, I have a lot of records in the query so duplicating it may be a bad idea.

Highlighted
Super User III
Super User III

Re: Calculate the average of values across several columns in same row?

You can add a column in the query editor like this:

 

Table.AddColumn(#"PreviousStep", "NNCount", each List.NonNullCount({[A],[B]}))

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper I
Helper I

Re: Calculate the average of values across several columns in same row?

Hi Imke,

 

Thank you for the response. This looks like a great solution; but, can you explain it in more detail, please? Do the "A" and "B" reference column names? So, would the formula for my data be,

 

Table.AddColumn(#"PreviousStep", "NNCount", each List.NonNullCount({[Bin (DSCR)],[Bin (NCF)], [Bin (OpEx)] ...}))

 

Highlighted
Helper I
Helper I

Re: Calculate the average of values across several columns in same row?

Thanks, rdurkin, this is a great solution.

Super User III
Super User III

Re: Calculate the average of values across several columns in same row?

That's correct: List.NonNullCount counts the number of items in your list that are not null. So you don't have to write a condition for every column separately.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
New Member

Re: Calculate the average of values across several columns in same row?

Greetings!

 

How can you average two columns generating random numbers at the same time. The average result is not correct in output. Is it possible or not?

 

Query

 

Field:           ITR                     PRIMER DFT MIN: Int((90-75+1)*Rnd([ITR])+75)      PRIMER DFT MAX: Int((90-5+1)*Rnd([ITR])+75)        AVE: ([PRIMER DFT MIN]+[PRIMER DFT MAX])/2

Table:          DFT Readings   

 

Run Output

ITR         PRIMER DFT MIN         PRIMER DFT MAX       AVE

1                     82                                   87                      86

2                     75                                   78                      76

3                     77                                   85                      81

 

Your response will be highly appreciated.

 

Thanks,

 

Alex

Highlighted
Helper I
Helper I

Re: Calculate the average of values across several columns in same row?

I have the same question for averaging over 3 columns, some cells having blank values.  I am unable to replicate this formula, can you help?

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors