Forum Discussion
Grouped Count Across Multiple Columns
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.
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.
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.
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.
bmarshall92 so according to that, what is the total count for your sample data; i.e.
#1st Majors + #2nd Majors + #3rd Majors
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).