Forum Discussion
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 | Major1 | Major2 | Major3 |
| 21FA | 100001 | POL | SOC | |
| 21FA | 100002 | BIO | ||
| 21FA | 100003 | BIO | ||
| 21FA | 100004 | BIO | ||
| 21FA | 100005 | POL | SOC | BSAD |
| 21FA | 100006 | MSC | ||
| 21FA | 100007 | EXS | ||
| 21FA | 100008 | EXS | ||
| 21FA | 100009 | EDU | SPE |
What I'm hoping to do is create a visualization or table in a report that would display the count of students who are enrolled with a major across all three columns (edit: and to clarify,by this I mean # of Majors = # 1st Majors + #2nd Majors + #3rd Majors) and am having trouble figuring out the appropriate Measure, table, or whatever I need to do it. In the long run this also should be something I can filter based on Term (so figure out how many people are enrolled as a Biology Major in the Spring/Fall of any given year) but I'm building a model of it using one semester's enrollment data for now.
My first thought was to do a measure of Count of Major 1, Major 2, Major 3 and then a Sum of those three, but wasn't sure what the best way of ensuring that is broken down by major (# BIO, # POL, etc.) or if that would be better as a table?
tldr: trying to get from the table above to this:
| Major | Total Enrollment |
| POL | 2 |
| BIO | 3 |
| MSC | 1 |
| EXS | 2 |
| EDU | 1 |
| SOC | 2 |
| BSAD | 1 |
| SPE | 1 |
Edit: So after experimenting on my end I'm finding that doing the measure process above (COUNTA(Major1)) and then doing a measure that's the sum of the three is getting me an accurate count of total majors, but now has the problem of how to visualize it as a Matrix table of First Major x Total Major is giving inaccurate numbers and is missing majors since not all majors are available as first majors.
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12 Replies
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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?
bmarshall92 does this work for you
Measure = CALCULATE ( COUNTROWS ( t2 ), FILTER ( t2, t2[Major1] <> BLANK () && t2[Major2] <> BLANK () && t2[Major3] <> BLANK () ) )Just to clarify, what is the t2 in that, the table name? I'm just asking cause I gave that code a try and was told there were 5 majors which is definitely not right so not sure if I messed up the code or something else.
Total Majors 2 = CALCULATE( COUNTROWS('Census Data'), FILTER( 'Census Data', 'Census Data'[STTR.MAJOR.CENSUS4.XXX_1] <> BLANK() && 'Census Data'[STTR.MAJOR.CENSUS4.XXX_2] <> BLANK() && 'Census Data'[STTR.MAJOR.CENSUS4.XXX_3] <> BLANK() ) )I actually was playing around with this while waiting and I found that the measures of Count of Major 1, 2, 3 and then adding those three together does seem to get what I want but doesn't play nicely in presentation because all columns are incomplete lists of majors so a matrix will miss some. I do have a separate table that is a list of all active majors, but no idea how to establish a relationship in a way that would let me use that list with that measure.
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 "major in any" would follow this
Measure = CALCULATE ( COUNTROWS ( t2 ), FILTER ( t2, t2[Major1] <> BLANK () || t2[Major2] <> BLANK () || t2[Major3] <> BLANK () ) )Also. you don't need to create any adiitional tables. the pbix is attached.
