Forum Discussion
average every 6 items
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.
FabG , Please find the attached file. Added index in Power Query. Sum of each type of category on 6th element
4 Replies
FabG , Please find the attached file. Added index in Power Query. Sum of each type of category on 6th element
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.
Hi amitchandak,
i managed to open the file and it works perfectly.
Thank you very much for your help.
also if you find some time, can you go through the process and the functions you used?
that would be really useful.
thank you.
