Calculate average across columns excluding the blanks

[klehar]

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 across the columns(for each student/rollno)

But the caveat is : if there is a blank then that doesn't go in the average.

E.g.

For roll 1 it would be (12+14+11+23)/4

For roll 5 it would be (17+17)/2

For roll 4 it would be (15+14+17)/3

For roll 3 it would be (14+16+13+0)/4

How can i do this in dax?

Also note that, when the cells contain 0 it should be counted in the average but not when it is blank(roll 3)

[NidhiBhusari]

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]

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

[v-rzhou-msft · Accepted solution]

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.

[avb_140391]

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]

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.