Splitting a Population by Demographics into Two Studies

[Darko_Giac]

I have what may be a bit of a tall order issue, and not sure whether it goes beyond the scope of this thread, but thought I would give it a shot.

I’m currently working that includes respondent ID (of which there are 972), Age Group, Region, Race, and Gender.

I am looking for a way to group each respondent in either “Study 1” or “Study 2”.

So for example, if I create that “Study 1” and “Study 2” label for each respondent, and I filter only to White males from the Midwest who are age 4 to 6 (let’s say there are a total of 20 that show up using those filter criteria), the sample needs to be split evenly (or evenly-ish). So half of those respondents would need to be in Study 1, and the rest in Study 2 (with no overlap, e.g., participant 1 can’t show up in both studies).

This stratification rule needs to hold true if I use different combinations of the other filters (so let’s say of those 972 respondents there are 13 Hispanic females who are from the South and are age 7 to 9), I would need to split up that sample so that 7 of those respondents are in Study 1 and the remaining 6 are in Study 2.

I’m not sure if this is the most easily done with Power Query/Pivot, but I’ve attached a sample excel spreadsheet to (hopefully) help better visualize the set-up of the data

Again, I’m not sure if this is outside of the scope of this forum, but thought I’d check in with some experts.

Sample Workbook

[v-juanli-msft · Accepted solution]

Hi @Darko_Giac 

Open Edit queries, add a index column from 1

Close&&apply

Create measures

Median = CALCULATE(MEDIAN([Index]),ALLSELECTED(Sheet1))

result final = IF(MAX([Index])<[Median],"Study 1","Study 2")

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Darko_Giac]

Hi @v-juanli-msft@!

I tried your solution and only got about halfway there.

I added the index column (from 1), created the calculated measure (median), however when I calculate the second column ("result final"), it categorises everyone as being in "Study 2". I'm sure I'm overlooking something, but I've attached a follow-up workbook..

Sample Workbook.pbix

[v-juanli-msft]

Hi @Darko_Giac 

Do you have to create a column for the final output?

It is correct when i create measures 

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Darko_Giac]

Hi @v-juanli-msft 

Yes! I actually did get it to work.

However, this solution gets me 3/4 of the way there. That is, it catogorizes perfectly when you select each individual category within each variable (Screen cap #1 below).

However, when everything is selected via the filters, (screen cap #2 below) and I paste that resulting table into another worksheet to make sure the categorization is working, that categorization seems to gets lost.

(ScreenCap #2)

So, I've selected all the filters. I then copied that full table into another worksheet and applied manual filters to try and match screen cap #1:

You can see that when I filter on Gender as “Male”, Race as “White”, Region as “West” and Age “7 to 9”, it doesn’t split that group up evenly anymore.

Is there a way the DAX formula could categorize evenly within groups without having to select them within the filters one by one?

[v-juanli-msft]

Hi @Darko_Giac 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[Darko_Giac]

@v-juanli-msft , yes, this worked very well!

[v-juanli-msft]

Hi @Darko_Giac 

As tested, it is a measure, it can work as expected, even i select all items in the slicer.

I  create a measure to show the percent of "Study 1".

Here is my file.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.