Forum Discussion

bmarshall92's avatar
bmarshall92
New Member
4 years ago
Solved

Grouped Count Across Multiple Columns

I'm working with a dataset of students where I need a total enrollment count based on majors. The problem is that majors are spread across multiple columns like below (fake data).   Term ID Ma...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi bmarshall92 ,

    According to your description, here's my solution.

    Create a new table.

    Table 2 = 
VAR _Major1=SUMMARIZE('Table','Table'[Major1],"Count",COUNT('Table'[Major1]))
VAR _Major2=SUMMARIZE('Table','Table'[Major2],"Count",COUNT('Table'[Major2]))
VAR _Major3=SUMMARIZE('Table','Table'[Major3],"Count",COUNT('Table'[Major3]))
VAR _Major=UNION(_Major1,_Major2,_Major3)
RETURN
FILTER(_Major,[Major1]<>BLANK())

    Then get the expected result.

    I attach my sample below for reference.

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

smpa01's avatar
smpa01
Community Champion
4 years ago

bmarshall92  please post sample data representative of the issue. t2 is the table name.  You originally had 3 columns which were all to be non-blank. Now, it is 5. 

bmarshall92's avatar
bmarshall92
New Member
4 years ago

I just realized I think there is a misunderstanding here cause I caught onto what you were providing. The code you provided is calculating how many people have 3 majors, essentially (which is how I got 5 because in my real data there's 5 people with triple majors).

 

What I need is "How many people have a X major regardless of whether it's their first, second, or third major?", which for any given major is = #1st Majors + #2nd Majors + #3rd Majors.

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    bmarshall92  so according to that, what is the total count for your sample data; i.e. 

    #1st Majors + #2nd Majors + #3rd Majors

    • bmarshall92's avatar
      bmarshall92
      New Member
      4 years ago

      So, for full time undergrads (which is what I counted in Excel for something else), there are 1800 majors. Doing COUNTA of each column and then adding together got me that total number but I'm now having trouble visualizing it by major. Can't do Matrix because any of the Major columns is incomplete, and it also seems to give inaccurate numbers when I do that anyway.

      I have another table that is just a list of majors, but not sure if that can be imported in and established with a relationship in anyway to this data set (it flags it as a "Many to Many" relationship, which is weird because there are no repeat values in the list of majors).

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        bmarshall92  I meant for the data set that you provided, what is your desired output?

        #1st Majors + #2nd Majors + #3rd Majors