Grouped Count Across Multiple Columns

[bmarshall92]

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.

[v-yanjiang-msft · Accepted solution]

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]

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?

[smpa01]

@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.

[smpa01]

@bmarshall92  does this work for you

Measure =
CALCULATE (
    COUNTROWS ( t2 ),
    FILTER (
        t2,
        t2[Major1] <> BLANK ()
            && t2[Major2] <> BLANK ()
            && t2[Major3] <> BLANK ()
    )
)

[bmarshall92]

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.

[smpa01]

@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]

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]

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

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

[bmarshall92]

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]

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

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

[bmarshall92]

Oh, sorry. It'd end up something like, with 9 students with 13 majors total:

Major	Total Enrollment
POL	2
BIO	3
MSC	1
EXS	2
EDU	1
SOC	2
BSAD	1
SPE	1

[bmarshall92]

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.