Forum Discussion
Add a value to random rows with specified quantity
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!
2 Replies
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 beSample = 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 🙂