Need help to convert an excel formula to DAX

[HariniSheshadri]

Hi,

I am trying to convert the below formula to a DAX calculated column using the mentioned data set. I want to calculate the mean scores for each subsection per user and apply the formula. 

DataSet:

Formula to be used: 100-((Mean score of subsection for each user *100)/4)

Any help would be appreciated in leading me in the right direction, thanks in advance. 

[v-yiruan-msft]

Hi @HariniSheshadri ,

You can create a calculated column as below to get it, please find the details in the attachment.

Column =
VAR _mean =
    CALCULATE (
        AVERAGE ( 'Table'[SubsectionScore] ),
        FILTER ( 'Table', 'Table'[User] = EARLIER ( 'Table'[User] ) )
    )
RETURN
    100 - ( ( _mean * 100 ) / 4 )

Best Regards

[HariniSheshadri]

Thanks for the response. But this formula dint work. It displays the same value for all the subsections in the list. 

[Coriel-11]

Hi @HariniSheshadri 
Sorry to hear that.
When you said in your original post that you wanted a "DAX calculated colum" did you mean the formula to add a column in "Data View", or did you mean a measure that you could add to a table in "Report View"?

I only ask because I recreated your data as part of making my initial reply and it seemed to work there. Alternatively, perhaps you could share the/a .pbix file so we can see if there's something we hadn't thought about.

Matt

[Coriel-11]

Hi @HariniSheshadri 

Try this

Formula =

100 - (25*CALCULATE(

    AVERAGE('Delete data'[SubsectionScore]),

    ALLEXCEPT('Delete data', 'Delete data'[User], 'Delete data'[Subsection]))  )

Matt