Group multiple rows in 3 categories for each mont

[dvhez]

My data looks like the image below. I have 60 different values for every month  and I'd like to sort them from highest to lowest (by BarCMgMed, for example) and divide them in three groups of 20 rows each, in order to calculate their average. My objective then is to have just three values where each one corresponds to the average of those groups of 20 rows. For example:

A    2019    Ene     Cardones 220    35.06

B    2019    Ene     Cardones 220    40.34

C    2019    Ene     Cardones 220    48.16

[parry2k]

@dvhez you can add index column in power query to make set 20 records and then average on that. If you share the sample data in excel file I can get you the solution.

[dvhez]

Thanks @parry2k!. Please let me know if you have any issue opening the attached file.

 https://drive.google.com/file/d/16oG8I0txTdEUvo6_54ENJocrSNjQ08ZC/view?usp=sharing

[parry2k]

@dvhez quick question, does it matter which 20 rows get grouped together?

[dvhez]

@parry2k The idea would be to group the highest 20 rows, then the 20 next and so on (sorted by BarCMgMed from lowest to highest)

[parry2k]

@dvhez here is what the reuslt looks like

[dvhez]

@parry2k that's exactly what I'd like to achieve!. Can you tell me how did you make it please?. Thanks!

[parry2k · Accepted solution]

@dvhez here is pbix file for you.

[dvhez]

@parry2k thanks a lot!. Do you know if it's posible to sort a column but by month? (my data has more months than just January, but sorting "CMgMid" column would mess up my "Month" column order.

[parry2k]

@dvhez you can surely sort it by month and in Data Model you can sort it by whatever way you want, sorting in PQ will not mess things up. Go ahead and try it and if need help, feel free to reach out.