Average on multiple columns Using DAX

[sravani9920]

Hello,
I wants to find the average over multiple columns, Below is the Excel sample data and Col1 to Col11 are the columns to find average and "Average Column" values are average of the Col1:Col11 after rounding and next column is Before rounding.
Note: I should replicate this average Function in the power BI using Dax
Second and third row contains only  numbers so while calculating average we don't find any issue in the power Bi as Excel 
Eg Col1+Col2------+Col11/11,
But first  and third row Contains both Numeric and non numeric it should take average for those Columns which are having numbers only 

1st row 690/7=98.57
4th row 790/8=98.75
Can you please Help me how to achive this logic in power Bi when row has both numeric and non numeric values, Sum all the numbers(By ignoring text) and divide by only column count which has numbers instead of taking all the 11 columns to find average.
I wants to show this Average in the Visualization like Table,Column Chart etc.
Thanks in advance.

Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10	Col11	Average
100	100	100	100	NA	NA	90	NA	100	NA	100	99	98.57143
100	100	100	100	100	100	100	80	90	0	60	85	84.54545
100	100	100	100	100	100	100	0	90	0	70	78	78.18182
100	100	100	100	100	?	90	NA	100	NP	100	99	98.75

[Ritaf1983 · Accepted solution]

Hi @sravani9920 

Power BI doesn’t calculate across cells like Excel – it works by columns, not by individual cells.
So to compute row-wise averages across multiple columns (while ignoring text), you need to reshape the data – it's a fundamentally different method.

What to do:

1. Use Unpivot Columns (Col1 to Col11) in Power Query.

2. Set the data type of the unpivoted column to whole number
   3. replace errors to null 
   You will get the table :
   

   After loading the data you can use DAX formula :
   
   Average_ = AVERAGEA('Table'[Value])

   Result:
   

   The pbix is attached you can follow my steps.
   More information about unpivot :

   https://www.youtube.com/watch?v=ESap6ptV8fI

   If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[v-veshwara-msft]

Hi @sravani9920 ,

May I ask if the solutions provided has addressed your needs? If so, please consider marking helpful reply as Accepted Solution to help others with similar queries.

If you need any further assistance, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @sravani9920 ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Also thanks to @Ashish_Excel for your response.

Thank you.

[Ashish_Excel · Accepted solution]

Hi,

This M code in Power Query works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col3", Int64.Type}, {"Col4", Int64.Type}, {"Col5", type any}, {"Col6", type any}, {"Col7", Int64.Type}, {"Col8", type any}, {"Col9", Int64.Type}, {"Col10", type any}, {"Col11", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Average(List.RemoveNulls(List.Transform(Record.ToList(_), each try Number.From(_) otherwise null))))
in
    #"Added Custom"

Hope this helps.

[v-veshwara-msft]

Hi @sravani9920 ,

Thanks for reaching out to Microsoft Fabric Community.

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thanks to @Ritaf1983 for your valuable response.

Thank you.

[Ritaf1983 · Accepted solution]

Hi @sravani9920 

Power BI doesn’t calculate across cells like Excel – it works by columns, not by individual cells.
So to compute row-wise averages across multiple columns (while ignoring text), you need to reshape the data – it's a fundamentally different method.

What to do:

1. Use Unpivot Columns (Col1 to Col11) in Power Query.

2. Set the data type of the unpivoted column to whole number
   3. replace errors to null 
   You will get the table :
   

   After loading the data you can use DAX formula :
   
   Average_ = AVERAGEA('Table'[Value])

   Result:
   

   The pbix is attached you can follow my steps.
   More information about unpivot :

   https://www.youtube.com/watch?v=ESap6ptV8fI

   If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly