Latest Enterprise DNA Initiatives


Groupby or Summarize

So what I am trying to accomplish is summarize a table to only show the total completed courses by student for each year. I can do this in a simple summarize formula that uses COUNTA. Where it gets complicated is I want to summarize by completed courses in a year but exclude any course where the grade is blank or contains a grade of I, F, W, or FX.

I started looking into the GROUPBY and then I looked into CALCULATETABLE so that I could insert filter logic but neither worked in achieving the desired outcome of one summarize row for the student by their ID number with the total of courses.

Below is where I paused, hoping someone might have some insight on what I am doing wrong. Each column I have listed in the the GROUPBY are columns I need to group the information by. I also thought I could use EVALUATE then FILTER then SUMMARIZECOLUMNS but that doesnt work.

Completed Courses =
GROUPBY (
    'WW Registrations',
    [Academic Year],
    [Student ID],
    [Academic Year End Academic Career],
    [Academic Year End Location Description],
    [Academic Year End Academic Plan No Roll-up Description],
    [AY-END Academic College - No Rollup (Student)],
    [Gender Description],
    [Age Groups],
    [Age],
    [Ethnicity (IPEDS)],
    [Course Grade Official],
    'WW Registrations'[AY End Status],
    'WW Registrations'[Classification-Primary Plan],
    'WW Registrations'[AY End Location Group],
    'WW Registrations'[Classification-Primary Plan (groups)],
    "Completed Courses", COUNTAX (
        CURRENTGROUP (),
        'WW Registrations'[Course Grade Official] <> ""
            || 'WW Registrations'[Course Grade Official] <> "I"
    )
)

Going to have to see the demo here to assist I think.

I really am not sure where to start as the formula seems long and I don’t know anything about the data model and the data.

Few big suggestions here.

This formula looks to complex to me. Can you simplify parts of this

Personally my formulas are rarely this big, because I’ve never found they need to be.

I also rarely use the GROUPBY function as well so I’m interesting why you feel you need to in this case.

I’m very confident something needs to be simplified here. It could be your model, but I’m not sure.

You should be able to get a result I believe using CALCULATE and FILTER (or maybe SUMMARIZE) somehow, but as I mentioned it’s difficult to say without seeing more.

Maybe setup a demo that just shows the scenario you are dealing with and we can work with that to offer suggestions for end formulas.

Thanks
Sam