dynamic variable to determine the distribution of data

[inbal]

Hi,

I would like to divide the lines in my data by numerical value.

I must save the value dynamically because the value changes every time rows are updated,

and then perform the partition.

Here is a small example:

Row number	Project	The value I want
001	Aaa	A
002	Bbb	A
003	Ccc	B
004	Ddd	B
005	Eee	B

When the project value is "Ccc" we would like to get the line number, say in this case “003”.

All rows above (1-2) will have a value of A And all the rows below (3-5) it will have a value of B.

How can I do this?

Thanks,

Inbal

[v-yuezhe-msft · Accepted solution]

@inbal,

You can create the following columns in your table. Please change the data type of Index column to whole number.

Index = Table[Row number]

number of Ccc = CALCULATE(FIRSTNONBLANK(Table2[Index],1),FILTER(Table2,Table2[Project]="Ccc"))

The value I want = IF(Table2[Index]<Table2[number of Ccc],"A","B")

Regards,
Lydia

[inbal]

Thanks!