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

The data is representative of the underlying data I'm dealing with: a list of students and their first, second, and third major across three separate columns. What I'm needing to present is a count of all students who have a major in any of the three columns. In Excel I did this by basically creating a table of all majors and then SUMIF statements for Major 1, Major 2, and Major 3 with a SUM across the row to get a total. I could probably do that here as well but was hoping to avoid having to create a whole new table but looking like that might be what I need to do to get it to visualize properly.