average every 6 items

[FabG]

Good morning,

i have a very short experience with Bi and i need some help to solve an issue:

i have a table of mix and relative values and i need to calculate average every 6 item per category. So for example on table below, need to calculate the average for the first 6 record listed as mix7, then the average for the following 6 value of mix 7. Obviously the calculation has to be valid for all the mixes listed with the same criteria (every 6 record).

in the eample below i the average every 6 records was by manual entry, just to make it clear.

Category	Value	RollingAvg
mix7	54
mix10	46
mix20	23
mix7	61
mix7	58
mix20	25
mix20	22
mix7	55
mix7	59
mix10	49
mix7	60	57.8
mix7	62
mix20	23
mix10	45
mix7	26
mix7	44
mix7	63
mix20	21
mix10	45
mix20	23
mix7	47
mix10	48
mix7	59	50.2

so that, when i apply a filter to the table, should look like this

Category	Value	RllngAvg
mix7	54
mix7	61
mix7	58
mix7	55
mix7	59
mix7	60	57.8
mix7	62
mix7	26
mix7	44
mix7	63
mix7	47
mix7	59	50.2

thanks in advance.

[amitchandak · Accepted solution]

@FabG , Please find the attached file. Added index in Power Query. Sum of each type of category on 6th element

[FabG]

also if you find some time, can you go through the process and the functions you used?

that would be really useful.

thank you.

[FabG]

Hi amitchandak,

i managed to open the file and it works perfectly.

Thank you very much for your help.

[FabG]

Hi amitchandak

when i try to open the filed a message says it was edited with a newer version of Bi, but i literally download the desktop version few days ago and no updates are available so far.