Forum Discussion

pvravestein's avatar
pvravestein
Icon for Helper I rankHelper I
5 years ago

Divide multiple columns averages in total average considering also blanks

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

3 Replies

    • pvravestein's avatar
      pvravestein
      Icon for Helper I rankHelper I
      5 years ago

      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

       

  • AnkitKukreja's avatar
    AnkitKukreja
    Icon for Super User rankSuper User
    5 years ago

    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.