Convert Excel formula to DAX

Hi Joakim_gg ,

If you want to achieve it using DAX in Power BI, you need to create a reference unpviot table like this in Power Query first:

Create a calculated column like this:

Result = 
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Reference table' ),
        FILTER (
            'Reference table',
            'Reference table'[Category] = EARLIER ( 'Table'[Category] )
        )
    )
VAR A =
    IF ( [Column1] = BLANK (), 0, [Column1] )
VAR B =
    IF ( [Column2] = BLANK (), 0, [Column2] )
VAR C =
    IF ( [Column4] = BLANK (), 0, [Column4] )
VAR D =
    IF ( [Column6] = BLANK (), 0, [Column6] )
VAR E =
    IF ( [Column7] = BLANK (), 0, [Column7] )
VAR F =
    IF ( [Column3] = BLANK (), 0, 5 - [Column3] )
VAR G =
    IF ( [Column5] = BLANK (), 0, 5 - [Column5] )
VAR H =
    IF ( [Column8] = BLANK (), 0, 5 - [Column8] )
VAR I =
    IF ( [Column9] = BLANK (), 0, 5 - [Column9] )
VAR result =
    IF ( _count > 0, ( A + B + C + D + E + F + G + H + I ) / _count, BLANK () )
RETURN
    result

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.