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.

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
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.

bmarshall92's avatar
bmarshall92
New Member
4 years ago

Thanks! This definitely helps, and I'll count it as a solution to the initial problem. In between the time I posted this and now some stuff came up and the original demands changed a bit so I went and used my real data to make a table in Excel that was essentially just a many-to-one relationship between majors and student ID where each row was:

Term | Student ID | Major | Major Type


Basically, it's a list of every currently enrolled major with information flagging it as whether it's a student's first/second/or third major. That turned out to work out really well because it provided data on not just the total number of majors but could then break down by type of major (sometimes we're interested in what programs often share majors). Do you have advice on how that could be created within PowerBI so it could be more automated?