Forum Discussion

sunilbeckham's avatar
sunilbeckham
Helper I
3 years ago
Solved

Changing measure calculation based on Column values.

Hello everyone

 

I have a student table similar to the below dummy columns that I'm trying to create a measure for. I'm trying to create a measure that will change it's calculation depending on the values in the column "Completion Years". The intent is to create a measure which will show the percentage of students, but the denominator will change depending on the column values of "File Source". Here's the measure that I have created so far:

 
Graduation Rate =
SWITCH(TRUE(), student[Completion Years] = "3 Years", DIVIDE(CALCULATE(COUNTA(student[Student Number]),student[Graduation Status] = "OT",student[Source] = "On Time"),CALCULATE(COUNTA(student[Student Number]),student[Graduation Status] = "OT",student[Source] = "On Time")),student[Completion Years] IN{ "4 Years","5 Years"},DIVIDE(CALCULATE(COUNTA(student[Student Number]),student[Graduation Status] IN {"OT", "ET"},student[Source] = "Extended Time"),CALCULATE(COUNTA(student[Student Number]),student[Graduation Status] IN {"OT","ET"} ,student[Source] = "Extended Time")),student[Completion Years])
 
However, the intellisence is not picking up the column name "Completion Years". Is there something that I'm missing out on?
Any help would be much appreciated.
Thanks.

 

Student NumberGraduation StatusSourceCompletion Years
101OTOn Time3 Years
102OTOn Time3 Years
103ETExtended Time4 Years
104ETExtended Time5 Years
105ETExtended Time5 Years

 

  • onurbmiguel_'s avatar
    onurbmiguel_
    3 years ago

    hi sunilbeckham 

    Try this measure: 

    VAR _compl_year = SELECTEDVALUE( Student[Completion Years] )
VAR _value =
    SWITCH(
        TRUE( ),
        _compl_year = "3 Years",
            DIVIDE(
                CALCULATE( COUNTA( Student[Student Number] ), Student[Graduation Status] = "OT", Student[Source] = "On Time" ),
                CALCULATE( COUNTA( Student[Student Number] ), Student[Graduation Status] = "OT", Student[Source] = "On Time" )
            ),
        _compl_year IN { "4 Years", "5 Years" },
            DIVIDE(
                CALCULATE(
                    COUNTA( Student[Student Number] ),
                    Student[Graduation Status] IN { "OT", "ET" },
                    Student[Source] = "Extended Time"
                ),
                CALCULATE(
                    COUNTA( Student[Student Number] ),
                    Student[Graduation Status] IN { "OT", "ET" },
                    Student[Source] = "Extended Time"
                )
            ),
        BLANK( )
    )
RETURN
    _value

     

     

    Best regards

    Bruno Costa | Power Participant

     

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍


    You can also check out BI4ALL's website and our data solutions!

2 Replies

  • onurbmiguel_'s avatar
    onurbmiguel_
    Power Participant
    3 years ago

    hi sunilbeckham 

    Try this measure: 

    VAR _compl_year = SELECTEDVALUE( Student[Completion Years] )
VAR _value =
    SWITCH(
        TRUE( ),
        _compl_year = "3 Years",
            DIVIDE(
                CALCULATE( COUNTA( Student[Student Number] ), Student[Graduation Status] = "OT", Student[Source] = "On Time" ),
                CALCULATE( COUNTA( Student[Student Number] ), Student[Graduation Status] = "OT", Student[Source] = "On Time" )
            ),
        _compl_year IN { "4 Years", "5 Years" },
            DIVIDE(
                CALCULATE(
                    COUNTA( Student[Student Number] ),
                    Student[Graduation Status] IN { "OT", "ET" },
                    Student[Source] = "Extended Time"
                ),
                CALCULATE(
                    COUNTA( Student[Student Number] ),
                    Student[Graduation Status] IN { "OT", "ET" },
                    Student[Source] = "Extended Time"
                )
            ),
        BLANK( )
    )
RETURN
    _value

     

     

    Best regards

    Bruno Costa | Power Participant

     

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍


    You can also check out BI4ALL's website and our data solutions!

    • sunilbeckham's avatar
      sunilbeckham
      Helper I
      3 years ago

      This was really helpful.

      Thank you so much for your time and input.