cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Darko_Giac Regular Visitor
Regular Visitor

Splitting a Population by Demographics into Two Studies

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Splitting a Population by Demographics into Two Studies

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")

1.png

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.

View solution in original post

7 REPLIES 7
Community Support Team
Community Support Team

Re: Splitting a Population by Demographics into Two Studies

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")

1.png

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.

View solution in original post

Darko_Giac Regular Visitor
Regular Visitor

Re: Splitting a Population by Demographics into Two Studies

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

Community Support Team
Community Support Team

Re: Splitting a Population by Demographics into Two Studies

Hi @Darko_Giac 

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

It is correct when i create measures 

10.png

 

 

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 Regular Visitor
Regular Visitor

Re: Splitting a Population by Demographics into Two Studies

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).

 

Capture.JPG

 

 

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)

Capture2.JPG

 

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:

 

Capture3.JPG

 

 

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?

Community Support Team
Community Support Team

Re: Splitting a Population by Demographics into Two Studies

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".

7.png

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.

Community Support Team
Community Support Team

Re: Splitting a Population by Demographics into Two Studies

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 Regular Visitor
Regular Visitor

Re: Splitting a Population by Demographics into Two Studies

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)