Add a value to random rows with specified quantity

[Anonymous]

Hi everyone! Is there a way to add a string of text to random rows with specified quantity? 

The "Sample" column below is what I want to achieve.

In this example, there are 4 rows with Red color, and the sampling % for red is 50%. Thus, for 2 random rows with red color (50% of 4) the text "for sampling" should be added.

Unit #	Color	Sampling %	Sample	Occurence	Required Sampling
1	Red	50%		4	2
2	Blue	30%	For sampling	1	1
3	Black	25%	For sampling	1	1
4	Red	50%	For sampling	4	2
5	Red	50%		4	2
6	Red	50%	For sampling	4	2

So far I have added a column for occurrence count

Occurence = COUNTX(FILTER('Unit', EARLIER ('Unit'[Color] ) = 'Unit'[Color]),'Unit'[Color])

then multiplying it with the % to get the required number of sampling rounded up

Required Sampling = ROUNDUP('Unit'[Sampling %]*'Unit'[Occurence],0)

but I can't figure out how to implement the required # of sampling to random rows

or if there's a way to create non-repeating random numbers 

Thanks in advance!

[Ahmedx · Accepted solution]

[Anonymous]

I was able to find a workaround for now by adding an index column then creating a column with the following formula :

Sampling Index = rankx(

    FILTER('Unit',EARLIER('Unit'[Color])='Unit'[Color]

         &&EARLIER('Unit'[Unit #])='Unit'[Unit #]),

        'Unit'[Index])

and then the Sample column would be

Sample = if('Unit'[Sampling Index]<='Unit'[Required Sampling],"For sampling",BLANK())

not sure if this only gets the first x for sampling, but I checked and it doesn't necessarily get the data in the same order as the raw data so this may work.

any other ideas are still welcome 🙂