Forum Discussion

Darko_Giac's avatar
Darko_Giac
Helper II
7 years ago
Solved

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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's avatar
Darko_Giac
Helper II
7 years ago

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's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      Darko_Giac
      Helper II
      7 years ago

      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's avatar
        v-juanli-msft
        Community Support
        7 years ago

        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.