How to show progress of selected total values in a column

[Dee]

Hi people,

I need help in figuring out, how to show the progress of students in the particular courses taken under a module.

Any help on how to achieve this will be highly appreciated

I have 3 tables:

1. Modules With Courses

Modules        Courses

1                     BBA, BBC,BBD,BBE,BBF

2                     BBB

2. Courses and student progress in that module

Courses:    

Names       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

3. Students progress per module

Name     Module 1                                                                                              Module 2

               enrolled                                 Progress                                                  enrolled  Progress

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

I would love to show per module the number of courses a student has enrolled in, as a fraction and their progress.

TIA!!

[Anonymous]

HI @Dee,

It is hard to share useful DAX formulas from your requirements and descriptions.

Please share some dummy data with a similar data structure and expected result to help us clarify your requirement and test to coding formula on it.

Regards,

Xiaoxin Sheng

[Dee]

Hi @Anonymous 

 I've edited the original post.

Thanks

[Anonymous · Accepted solution]

Hi @Dee,

You can refer to the following measure formulas and matrix design to achieve your requirement:

Progress = 
VAR moduleList =
    CALCULATE (
        CONCATENATEX ( VALUES ( Modules[Courses] ), [Courses], "," ),
        ALLSELECTED ( Modules ),
        VALUES ( Modules[Modules] )
    )
VAR total =
    CALCULATE (
        PATHLENGTH(SUBSTITUTE(CONCATENATEX(VALUES(Modules[Courses]),[Courses],","),",","|")),
        ALLSELECTED ( Modules ),
        VALUES ( Modules[Modules] )
    )
VAR actual =
    CALCULATE (
        COUNT ( Courses[student enrolled] ),
        FILTER ( ALLSELECTED ( Courses ), SEARCH ( [Names], moduleList, 1, -1 ) > 0 ),
        VALUES ( Courses[student enrolled] )
    ) + 0
RETURN
    actual & "/" & total


avg grade =
VAR summary =
    SUMMARIZE (
        Courses,
        [student enrolled],
        "AVG", CALCULATE (
            AVERAGE ( Courses[Grade] ),
            FILTER (
                ALLSELECTED ( Courses ),
                SEARCH (
                    [Names],
                    CONCATENATEX ( VALUES ( Modules[Courses] ), [Courses], "," ),
                    1,
                    -1
                ) > 0
            ),
            VALUES ( Courses[student enrolled] )
        )
    )
RETURN
    AVERAGEX ( summary, [AVG] ) + 0

Matrix design:

Regards,

Xiaoxin Sheng

[Dee]

Thank you so much for this, seems like it's doing exactly what am looking for.

Could you kindly share with me the PBIX, to better understand the formulae used?

Thanks once again for going through all this trouble.

[Anonymous]

Hi @Dee,

In fact, you only need to create two measures on your table and rename its fields with your own table field names.

I also attached the same file below, you can check if it meets for your requirement.

Regards,

Xiaoxin Sheng

[amitchandak]

@Dee , if this can help you https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381544?tab=Overview

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490