Calculate Field in Power Pivot using DAX

[powerwill]

Hello,

Office 365 user here.

With a Regular Pivot, I will not take an average of the 'Score%', doing so would be incorrect, hence I use a 'Caclulated field' to show the result obtained by dividing 'Marks Obtained/Total Marks'.

I have now moved my data to a Power Query/Data Model, as I want to use Power Pivot Measures, and I understand the 'Calculated Field' will not work there and is greyed out.

I need to replicate this in DAX, so that my PowerPivot - Measure would do exactly this: 'Marks Obtained/Total Marks' for each student.

My Expression in the 'Measures' window looks like this: =AVERAGE(Query1[Marks Obtained]/Query1[Total Marks]) and it throws an error.

"This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Any help is appreciated!

 

[powerwill]

Figured this myself. Thank you for your attention!

 

[smozgur]

Good to hear you figured it out yourself.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[powerwill]

@smozgur apologies. will keep this in mind.

So I created two seperate measures to SUM the Total Marks & Marks Obtained. Then used the DIVIDE fx in DAX to get the actual score.