How to show actual values as fractions instead of average

[Dee]

Hi all,

I seem to be stuck with a problem, I asked previously but it seems I didn't phrase it properly as at that point I wasn't exactly sure how to approach the issue.

Anyhu, hears the issue.

I seem to be having a table containing students, courses and the grades they've gotten.

example,

Courses:    

Name       student enrolled         Grade              status

BBA                A                              50                     not complete

BBA                B                               20                    not complete

BBB                A                               20                    not complete

BBC                A                               10                    not complete

BBD                A                                100                 done

BBG                A                                 0                     not complete

The courses have a parent module, and a student can be enrolled in multiple courses under a module, I'm currently getting their grades by doing an average on their progress, as shown below:

                                MODULE1                                         Module 2           

Name    COURSE          AVG PERFORMANCE              COURSE          AVG PERFORMANCE    

However, I'd love to show, the number of courses a student has enrolled in the module over the total, and then get the progress of the grade, regardless of the progress as shown below.

                                   Module 1                                                               Module 2

Name               enrolled                                 Progress                                          enrolled  Progress

A            3/5 t enrolled in three)      Avg of grade()                                            1/1(Because the module only                                                                                                                                       has one course)

Any help on how to achieve this would be highly appreciated, thanks.

[v-alq-msft]

Hi, @Dee 

Based on your description, I created data to reproduce your scenario.

Courses:

ModuleCourse:

You may create two measures as below.

enrolled = 
var _table = 
SUMMARIZE(
    Courses,
    Courses[Names],
    Courses[Student enrolled],
    Courses[Grade],
    "Ismodule1",
    var _course = Courses[Names]
    var _result = 
    CONTAINSSTRINGEXACT(
        CALCULATE(
            MAX(ModuleCourse[Courses]),
            FILTER(
                ALL(ModuleCourse),
                ModuleCourse[Modules] = 1
            )
        ),
        _course
    )
    return
    IF(
        _result,
        1,0
    ),
    "Ismodule2",
    var _course = Courses[Names]
    var _result = 
    CONTAINSSTRINGEXACT(
        CALCULATE(
            MAX(ModuleCourse[Courses]),
            FILTER(
                ALL(ModuleCourse),
                ModuleCourse[Modules] = 2
            )
        ),
        _course
    )
    return
    IF(
        _result,
        1,0
    )
)
var _stu = SELECTEDVALUE(Courses[Student enrolled])
var _module = SELECTEDVALUE(ModuleCourse[Modules])

return
IF(
    ISFILTERED(Courses[Student enrolled]),
    IF(
        _module = 1,
        COUNTROWS(
            FILTER(
                _table,
                [Ismodule1] = 1&&
                [Student enrolled] = _stu
            )
        )&"/"&
        COUNTROWS(
            FILTER(
                ALL(Courses),
                [Student enrolled] = _stu
            )
        ),
        IF(
            _module = 2,
            var _re =
            COUNTROWS(
                    FILTER(
                        _table,
                        [Ismodule2] = 1&&
                        [Student enrolled] = _stu
                    )
            )
            return
            IF(
                _re=BLANK(),
                0,
                _re
            )&"/"&
            COUNTROWS(
                FILTER(
                    ALL(Courses),
                    [Student enrolled] = _stu
                )
            )
        )
    )
)

Avg of grade = 
var _table = 
SUMMARIZE(
    Courses,
    Courses[Names],
    Courses[Student enrolled],
    Courses[Grade],
    "Ismodule1",
    var _course = Courses[Names]
    var _result = 
    CONTAINSSTRINGEXACT(
        CALCULATE(
            MAX(ModuleCourse[Courses]),
            FILTER(
                ALL(ModuleCourse),
                ModuleCourse[Modules] = 1
            )
        ),
        _course
    )
    return
    IF(
        _result,
        1,0
    ),
    "Ismodule2",
    var _course = Courses[Names]
    var _result = 
    CONTAINSSTRINGEXACT(
        CALCULATE(
            MAX(ModuleCourse[Courses]),
            FILTER(
                ALL(ModuleCourse),
                ModuleCourse[Modules] = 2
            )
        ),
        _course
    )
    return
    IF(
        _result,
        1,0
    )
)

var _stu = SELECTEDVALUE(Courses[Student enrolled])
var _module = SELECTEDVALUE(ModuleCourse[Modules])

return
IF(
    ISFILTERED(Courses[Student enrolled]),
    IF(
        _module = 1,
        AVERAGEX(
            FILTER(
                _table,
                [Ismodule1] = 1
            ),
            [Grade]
        )
        ,
        IF(
            _module = 2,
            AVERAGEX(
                FILTER(
                    _table,
                    [Ismodule2] = 1
                ),
                [Grade]
            ) 
        )
    )
)

Result:

Here is pbix .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dee]

@v-alq-msft Thank you for this.

However, from the measure it seems I'll have to manually add the modules as they increase. Is there a way toadjust the measure for it to automate that process.

This will be for the cases where I have module 3,4,5 and 6 added.

Than you.

[Anonymous]

Hi @Dee.

Have you opened my file with the solution? I attach it again...

Best
D

[Dee]

@Anonymous ,

Sorry, I hadn't seen the attached PBIX.

It seems to be solving another issue, I had with my data a million thanks.

Will adjust the question to suit my dynamic data as need be, or maybe start a new thread.

Thanks

[Anonymous]

@Dee, please mark the post that is the answer as THE answer ("Accept as Solution" button below the post) and it would be nice if you could kudo the post as well.

Many thanks.

Best
D

[Anonymous]

This should do what you want. If not, please adjust to your needs.

Best

D