I need to get a Random Sample from data within a pivot table

Good morning I am trying to get a weekly random sample from a list within a pivot table. My columns are Week, doc type, doc ID. I need a random sample every week of one doc ID of every doc type. I would like the cell value returned (doc ID) vs. a rand number. Since every week I'll take a 25% sample size of each doc type, it would be helpful if I could avoid duplicates. Can this be done? The volume per week and doc type vary so the amount of random samples needed per week will vary as well. Here is a mock up of the pivot table data. Thank you!

May be add helper column(s) in the source data or in data model.

Using RAND() function. Then add percentile column. Filter for 25% on Pivot Table.

But it's bit hard to help you without sample workbook. See yellow banner at the top for details.

I'm a bit confused, you have sample size with decimal value...

I assume that you need 2 Unique IDs if sample size = 1.25, 1 if it's 0.25 etc. Correct?

Also, method I indicated in my previous post can't be used here, since you are only interested in distinct (unique) list of ID and not whole list in raw data.

Are you still using Excel 2010? Or are you using more recent version?

Yes, that helps. I'd recommend updating your profile to reflect your current version (Office 365, 32bit).

With Office 365, you should have access to data model and DAX formula.

As well as access to new array formulas; Unique(), Filter() etc.

In your case... best bet is to create data model and few DAX measures to accomplish your need (using RAND(), SAMPLE() and some variables in DAX measure).

I'm out of time for the day, but will see if I have time tomorrow to create sample set up from your data, if others haven't helped you by then.

Oh, in previous post I mentioned SAMPLE(), but I forgot that in Excel data model, you can't create calculated table using DAX.

It's not ideal, but instead, all sampling are done purely in PowerQuery (M code).

This method skips Piovt Table and generates table with random sample.

Complete M code for reference.

Please Login or Register  to view this content.

Most of the steps you can follow in Query Editor, using "Applied Steps" pane.

See attached sample. Each time you refresh the query table, it will return with new random sample.

Key Concepts:
- Index column must be added before "Rand" column is added. This is to ensure evaluation of Number.Rand() in row context and not in table context.
- myList column holds subset table of table filtered on Group By columns. It is then sorted by Rand column to extract first N. Where N is [SampleSize].

You are welcome and thanks for the rep.
If you are satisfied with the solution provided, please mark the thread as solved using thread tools found at top of your initial post.