Divide multiple columns averages in total average considering also blanks

[pvravestein]

I calculate averages in my table and also I want a Total average from those averages.

But sometime I have no value, then then total average must give the total average from the other averages without the blank. 

Below is an example where you see an Total average of 1.6 (8+0+0+0+0/5 = 1.6).

But I want this Total number to be 8 (8+0+0+0+0/1).

What to do?

Thanks

[AnkitKukreja]

Hi @pvravestein 

You can follow the below approach as well:

Unvpivot your columns in Power Query or you can create new table using the below dax.

Avg_Data =

UNION(

    SELECTCOLUMNS( 'Average_Data' , "A" , "A" , "AvgData" , 'Average_Data'[A] ),

    SELECTCOLUMNS( 'Average_Data' , "B" , "B" , "AvgData" , 'Average_Data'[B] ),

    SELECTCOLUMNS( 'Average_Data' , "C" , "C" , "AvgData" , 'Average_Data'[C] ),

    SELECTCOLUMNS( 'Average_Data' , "D" , "D" , "AvgData" , 'Average_Data'[D] ),

    SELECTCOLUMNS( 'Average_Data' , "E" , "E" , "AvgData" , 'Average_Data'[E] )

    )

And post that just simply use AVERAGE( Table[E] ) and you will get the desired result as average ignores the blank values.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

[amitchandak]

@pvravestein , Try like

averagex(filter(Table, Table[Col1]<> 0 && not(isblank(Table[Col1]))),[Table[Col1]])

[pvravestein]

Great, thanks.  But forgive me, I am new to DAX and I cannot get it to work.

My table name is change, column names are on top of my screenshot. What will it look like with all data?

Many thanks