Forum Discussion

WJ876400's avatar
WJ876400
Helper IV
6 years ago
Solved

Measure for colour coding

Hi   I have the below table, it shows  3 students and 3 modules, each module has two elements to it. The 4th column shows what % that contributes to the grade.  Below is what I have done on excel a...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi WJ876400 

     

    I just renamed the measures so you can tell them. 1 point need to confirm that Total Mark =SUM(G2:G7) you mentioned before should be named as Total Mark towards Total Grade (I use TotalMarkTowards in measure), and the real Total Marks = SUM(E2:E7)

     

    Then total Mark Towards for each Student which adds up their marks towards total grade =: 

    Measure = [Marks towardsTotalGrade]+[ToTalMarktowards]

    And the conditional color based on it. do you mean like this below?

    You can refer to this similar post for reference: https://community.powerbi.com/t5/Desktop/Conditional-formatting-on-Strings-table-cells/td-p/784516

v-diye-msft's avatar
v-diye-msft
Community Support
6 years ago

Hi WJ876400 

 

Based on your logic, please use below measures:

Measure = CALCULATE(SUM('Table (2)'[Mark]),ALLEXCEPT('Table (2)','Table (2)'[Name]))
Measure 2 = MAX('Table (2)'[Weighting towards 100% Module])*MAX('Table (2)'[Mark])/[Measure]
Measure 3 = CALCULATE(SUM('Table (2)'[Weighting Factor]),ALLEXCEPT('Table (2)','Table (2)'[Name]))
Measure 4 = var a = CALCULATE(COUNT('Table (2)'[Mark]),FILTER(ALLEXCEPT('Table (2)','Table (2)'[Name]),[Mark]<40))
var b = CALCULATE(AVERAGE('Table (2)'[Mark]),ALLEXCEPT('Table (2)','Table (2)'[Name]))
Return
IF(a>0,"Red",IF(b>=60,"Green","Amber"))

 

PBIX attached.

  • WJ876400's avatar
    WJ876400
    Helper IV
    6 years ago

    Hi v-diye-msft 

     

    Thank you for sending this i will work through it and see if it works. How do I get a total Mark for each Student which adds up their marks towards total grade? And then have the colour based on that?

     

    thanks again

    • v-diye-msft's avatar
      v-diye-msft
      Community Support
      6 years ago

      Hi WJ876400 

       

      I just renamed the measures so you can tell them. 1 point need to confirm that Total Mark =SUM(G2:G7) you mentioned before should be named as Total Mark towards Total Grade (I use TotalMarkTowards in measure), and the real Total Marks = SUM(E2:E7)

       

      Then total Mark Towards for each Student which adds up their marks towards total grade =: 

      Measure = [Marks towardsTotalGrade]+[ToTalMarktowards]

      And the conditional color based on it. do you mean like this below?

      You can refer to this similar post for reference: https://community.powerbi.com/t5/Desktop/Conditional-formatting-on-Strings-table-cells/td-p/784516

      • WJ876400's avatar
        WJ876400
        Helper IV
        6 years ago

        Hi v-diye-msft 

         

        thanks for your help with this. How did you calculate the TotalMarksTowards column. I have the below so far but cant seem to add up the Mark towards for each student