Re: How to show actual values as fractions instead...

Dee · ‎04-12-2020

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 · ‎04-13-2020

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 · ‎04-16-2020

@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 · ‎04-16-2020

Hi @Dee.

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

Best
D

Dee · ‎04-16-2020

@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 · ‎04-16-2020

@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