Average on multiple columns Using DAX

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

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.