Forum Discussion
Pull Random 3% of Data
Alright, I remember how I did this previously. Took me a while, but I knew it could be done. Foregoing the half Red and half Blue on my wishlist, the way to create a new table with a random n% of rows from an existing table is to create a measure within the existing table as follows (using 3% as an example)
3% = CALCULATE(DISTINCTCOUNT('Table1'[Order Number])*.03)
Then create a new Table as using that measure:
3% = SAMPLE('Table1'[3%], 'Table1',0)
Then I can create a table visual based off of my new table, and add a slicer to select the Customer I want and it will pull a random 3% of that customer's orders.
Thanks all hope this helps someone else.
Hi E_Hutch
One way is to add a random number column, sort by the column, then select top rows accordingly...it is done in Power Query, you can use DAX to do it as well, Power Pivot in Excel, the same logic
Table.FirstN( Table.SelectRows(Table, each [Customer ID]=1 and [Color]="Red"),4)
&Table.FirstN( Table.SelectRows(Table, each [Customer ID]=2 and [Color]="Red"),2)
&Table.FirstN( Table.SelectRows(Table, each [Customer ID]=3 and [Color]="Red"),6)
&Table.FirstN( Table.SelectRows(Table, each [Customer ID]=1 and [Color]="Blue"),5)
&Table.FirstN( Table.SelectRows(Table, each [Customer ID]=2 and [Color]="Blue"),2)
&Table.FirstN( Table.SelectRows(Table, each [Customer ID]=3 and [Color]="Blue"),6)
Hello! Thanks for your help, though I am not sure how feasible this option is. There are thousands of Customer ID's so I would not be able to enter them all in that way. Additionally, I don't think selecting top rows would work because I need to pull as close to 3% as possible which would differ each month due to change in volume. Thank you for your help though!