topic Re: Divide multiple columns averages in total average considering also blanks in DAX Commands and Tips

Divide multiple columns averages in total average considering also blanks

pvravestein — Wed, 13 Jan 2021 15:03:12 GMT

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

amitchandak — Thu, 14 Jan 2021 08:17:39 GMT

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

AnkitKukreja — Thu, 14 Jan 2021 08:31:15 GMT

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.

pvravestein — Thu, 14 Jan 2021 08:33:03 GMT

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