Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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..
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.
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?
Hi @Darko_Giac
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.