Forum Discussion

klehar's avatar
klehar
Icon for Helper V rankHelper V
5 years ago
Solved

Calculate average across columns excluding the blanks

Hi,   Roll Science Physics Biology Maths 1 12 14 11 23 2 13 15 12 16 3 14 16 13 0 4 15 null 14 17 5 null 17 null 17   I want to calculate the average ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi klehar 

    Try this code to create a calculated column.

     

    Avg =
VAR _T =
    ADDCOLUMNS (
        'Table',
        "C_S", IF ( 'Table'[Science] <> BLANK (), 1, 0 ),
        "C_P", IF ( 'Table'[Physics] <> BLANK (), 1, 0 ),
        "C_B", IF ( 'Table'[Biology] <> BLANK (), 1, 0 ),
        "C_M", IF ( 'Table'[Maths] <> BLANK (), 1, 0 )
    )
VAR _Sum = 'Table'[Science] + 'Table'[Physics] + 'Table'[Biology] + 'Table'[Maths]
VAR _Count =
    SUMX (
        FILTER ( _T, [Roll] = EARLIER ( 'Table'[Roll] ) ),
        [C_S] + [C_P] + [C_B] + [C_M]
    )
VAR _Avg =
    DIVIDE ( _Sum, _Count )
RETURN
    _Avg

     

    Result is as below.

    Best Regards,
    Rico Zhou

     

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

NidhiBhusari's avatar
NidhiBhusari
Icon for Helper IV rankHelper IV
5 years ago

Hi klehar 

In Power Query first do the unpivoting of the table

Once done use the DAX , 

Col =

Var A = CALCULATE(SUM('Table'[Value]),GROUPBY('Table','Table'[Roll]))
Var B = A/5
return B


Output:-

Let me know if its working for you and giving correct output.

 

klehar's avatar
klehar
Icon for Helper V rankHelper V
5 years ago

Hi Nidhi,

 

I want the solution to be purely DAX

The reason is : In the actual data, all my subjects are calculated fields derived in DAX itself

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi klehar 

    Try this code to create a calculated column.

     

    Avg =
VAR _T =
    ADDCOLUMNS (
        'Table',
        "C_S", IF ( 'Table'[Science] <> BLANK (), 1, 0 ),
        "C_P", IF ( 'Table'[Physics] <> BLANK (), 1, 0 ),
        "C_B", IF ( 'Table'[Biology] <> BLANK (), 1, 0 ),
        "C_M", IF ( 'Table'[Maths] <> BLANK (), 1, 0 )
    )
VAR _Sum = 'Table'[Science] + 'Table'[Physics] + 'Table'[Biology] + 'Table'[Maths]
VAR _Count =
    SUMX (
        FILTER ( _T, [Roll] = EARLIER ( 'Table'[Roll] ) ),
        [C_S] + [C_P] + [C_B] + [C_M]
    )
VAR _Avg =
    DIVIDE ( _Sum, _Count )
RETURN
    _Avg

     

    Result is as below.

    Best Regards,
    Rico Zhou

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I have a similar issue, however there seems to be an error at the earlier function.

      My data:

       

      S_5or6 =
      VAR _T =
          ADDCOLUMNS (
              'vw_QuestQuestionAnswersCategories',
              "[C5]", IF ('vw_QuestQuestionAnswersCategories'[5] <> 0,1,0),
              "[C6]", IF ('vw_QuestQuestionAnswersCategories'[6] <> 0,1,0)
          )

      VAR _Sum = sum('vw_QuestQuestionAnswersCategories'[5]) + sum('vw_QuestQuestionAnswersCategories'[6])

      VAR _Count =
      SUMX(
          FILTER( _T,[Full_QuestionText] = EARLIER('vw_QuestQuestionAnswersCategories'[Full_QuestionText)),
          [C5] + [C6]
      )    

      VAR _Avg =
          DIVIDE ( _Sum, _Count )
      RETURN
          _Avg
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        3 years ago

        Hi,

        In the Query Editor, you should select the first 2 columns, right click and select "Unpivot Other Columns".  Thereafter a simple average measure should work

        Avg = average(Data[Value])

        Hoep this helps.