Custom column by dividing a column depending on a field [M-query]

[Anonymous]

Hi dear rescuers, 

I'm trying to divide a column based on another field. 

I would like to divide the rating field by the count of the subject to the corresponding category.  For example, the rating for science is 300.  I merged the subject column to category and rating, so it created multiple rows of 300. I would like to achieve the last column. where the original value of science is divided into equal parts by the count of the corresponding subject. 

Thanks in advance for your help 🙂 

[drewlewis15 · Accepted solution]

It might be eaiser to solve this using DAX, but the steps below should solve your issue using M.

Group by Category and choose to aggregate by "Count" and "All Rows"

Then expand all rows (excluding Category), and you should end up with the following:

You can now create a custom column dividing your sum column by your count column.

End Result:

[Anonymous]

Thanks a lot. It worked 🙂