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.

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.