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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MichalHoltz
Frequent Visitor

How to do an average of multiple columns?

I have 3 columns and would like to get the average of 3 of them. How do I do that?

App1App2App3
121
321
235
345
543
254
1 ACCEPTED SOLUTION

@MichalHoltz you right!

 

what about 

 

(AVERAGE(Table2[SAP SRM Functionality])+average(Table2[SAP SRM Productivity])+average(Table2[SAP SRM Quality])+average(Table2[SAP SRM Support]))/4?

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

14 REPLIES 14
dang
Advocate I
Advocate I

To be fair, dividing the sum of averages by the number of averages skews the data if each average does not contain the same number of records. In my mind, a more exact method would look something like (Sum(Table2[SAP SRM Functionality]) + Sum(Table2[SAP SRM Productivity]) + Sum(Table2[SAP SRM Quality]) + Sum(Table2[SAP SRM Support])) / (Countrows(Table2[SAP SRM Functionality]) + Countrows(Table2[SAP SRM Productivity]) + Countrows(Table2[SAP SRM Quality]) + Countrows(Table2[SAP SRM Support]))

tjm4q2
Frequent Visitor

You are the only one correct on this thread -- notwithstanding missing values that saturate your counts, taking the average of the summation of averages (otherwise known as the mean of means) IS NOT the same as taking the average of two columns worth of data; there are actually three common ways to take the average when using multiple columns and @dang has utilized the most correct way.

tjm4q2
Frequent Visitor

Update: An even more correct way using DAX would be to use a summarize(sumx()) statement.

Sumx doesn't work on variable tables

jimmy66
Regular Visitor

I have the same issue i want to add columns , but the average should only be for columns that have a value , i can't see what answer was proposed

 

Anonymous
Not applicable

I don't know the full model, but wouldn't it make more sense to change the datamodel into a thin fact table with App1-3 as a category, instead of building measures like this?

 

Would seem to be more alligned with best practice.

 

 

vanessafvg
Super User
Super User

@MichalHoltz

 

create a measure;

 

measure = average(sum(col1) + sum(col2) +sum(col3))





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thanks. I tried this but I get an error message- The AVERAGE function only accepts a column reference as an argument

What am I missing (I'm new to Dex)?

 

Measure = AVERAGE(SUM(Table2[SAP SRM Functionality])+SUM(Table2[SAP SRM Productivity])+SUM(Table2[SAP SRM Quality])+SUM(Table2[SAP SRM Support]))

@MichalHoltz you right!

 

what about 

 

(AVERAGE(Table2[SAP SRM Functionality])+average(Table2[SAP SRM Productivity])+average(Table2[SAP SRM Quality])+average(Table2[SAP SRM Support]))/4?

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




This doest work if one value is missed or 0 - may be any solutuon for that?

 

thnaks

ALeks

Exactly. Is there a way to solve this problem? Have the same problem!

Anonymous
Not applicable

@ 

Is there a way of averaging multiple columns via range instead of listing them all?

Anonymous
Not applicable

Thanks it worked

 

@vanessafvg it works! Thanks a lot!!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors