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  does this work for you

Measure =
CALCULATE (
    COUNTROWS ( t2 ),
    FILTER (
        t2,
        t2[Major1] <> BLANK ()
            && t2[Major2] <> BLANK ()
            && t2[Major3] <> BLANK ()
    )
)
  • bmarshall92's avatar
    bmarshall92
    New Member
    4 years ago

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