Standard deviation for custom number of grouped values in DAX

[RSip]

Hello guys,

I need to calculate standard deviation in every row for current rows value in column "Liters" and 4 rows below it while filtering by group. For example: first row is of group "A" and rank "8", so we take values coloured in magenta and if we calculate standard deviation in excel, it gives the answer 10.73. Those rows, which cannot form groups of 5, should remain 0 or null. "Standart deviation" is the column I am trying to achieve.

It seems like it should be an easy task, but I've been stuck on this for quite some time. I saw that a lot of people are suggesting just filtering in visuals, but i can not do that, because I will need these values to calculate further columns.

Since the information I am working with is confidential, I have created a dummy table:

Date:	Liters:	Group:	Rank:	Standard deviation:
2023-11-08	31,12	A	8	10,73
2023-11-07	34,44	A	7	14,15
2023-11-07	42,89	C	8	12,46
2023-11-05	44,51	C	7	12,56
2023-10-21	21,89	C	6	12,58
2023-10-06	29,56	B	6	20,33
2023-10-03	32,59	A	6	12,20
2023-09-20	57,21	A	5	12,59
2023-09-15	51,11	A	4	0,00
2023-09-09	40,03	B	5	19,07
2023-09-01	61,13	C	5	6,43
2023-08-27	21,15	B	4	0,00
2023-08-24	42,82	C	4	0,00
2023-08-23	80,15	B	3	0,00
2023-08-17	70,16	A	3	0,00
2023-08-15	56,61	A	2	0,00
2023-08-13	43,58	A	1	0,00
2023-08-10	47,78	B	2	0,00
2023-08-05	48,81	B	1	0,00
2023-07-25	46,56	C	3	0,00
2023-07-14	45,53	C	2	0,00
2023-07-01	51,13	C	1	0,00

Any suggestions would highly be appreaciated.

[lbendlin]

for current rows value in column "Liters" and 4 rows below it while filtering by group

Power BI has no such concept. You need to bring your own sort order.  Ideally your data already has an index column (or combination of columns) that can be used for that purpose.

Please clarify.

[RSip]

What I described in an unclear way, was probably "Top N" function, as the top row should be the current row, and total rows returned should be 5. But I was unsuccessful in implementing this myself.

Every row is sorted, indexed and ranked in groups, I just need to find a way to filter them to get the desired results.

[RSip · Accepted solution]

Nevermind, I figured out the answer myself, used this formula:

TABLE = STDEVX.P(FILTER(TABLE,TABLE[GROUP]= EARLIER(TABLE[GROUP]) && EARLIER(TABLE[RANK])>= (TABLE[RANK])  && EARLIER(TABLE[RANK])<= (TABLE[RANK])+4),TABLE[LITERS]).
Formula grabs values from current row and 4 filtered rows below it, calculates standard deviation and returns scalar value.

[lbendlin]

Consider using WINDOW rather than EARLIER.