Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Conditional Formatting based on calculated measure

So its a simple requirement where I need the backrgound colour of a matrix cell to change based on an input value (parametre)

 

The idea is if I input 70 into a parameter, the cells would colour the backgound of the cell that is above 70% (calculated measure) in RED.

 

How can this be achieved? looks like the rules method only allows for hardcoded values. Is there a workaround for this? See screen below for what im trying to acheive;

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi Anonymous ,

    Based on my test, you could refer to below steps:

    Create three parameters:

    Create a measure:

    Measure = var V=CALCULATE(SUM('Table'[Value]))
return IF(V>R[R Value],"#FD625E",
            IF(V>[A Value]&&V<R[R Value],"#F2C80F",
                IF(V>G[G Value]&&V<R[R Value],"#01B8AA")))

    Using the conditional formatting withe the above measure for your matrix:

    Now you could change the parameters for your colored visual.

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

5 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous ,

    Based on my test, you could refer to below steps:

    Create three parameters:

    Create a measure:

    Measure = var V=CALCULATE(SUM('Table'[Value]))
return IF(V>R[R Value],"#FD625E",
            IF(V>[A Value]&&V<R[R Value],"#F2C80F",
                IF(V>G[G Value]&&V<R[R Value],"#01B8AA")))

    Using the conditional formatting withe the above measure for your matrix:

    Now you could change the parameters for your colored visual.

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Genius v-danhe-msft , thank you very much for this solution.

       

      Any ideas how I can default all other blank values as grey or something similar? seems like the else in the if statement doesnt allow for this?

       

      See below of my version:

       

       
      ColourLogic = VAR V= [AboveSD%s]*100
return 
SWITCH(TRUE(),
            V>=[%Red_Param Value],"#FF3333",
            V>='%Amber_Param'[%Amber_Param Value],"#FFCE33",
            V>='%Green_Param'[%Green_Param Value], "#71FF33", 
            ISBLANK(V),"#CCCCCC")

      Its the else bit that doesnt appear to work. Ive tried without the ISBLANK also.

       
      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous ,

        Could you have refered my measure:

        Measure = var V=CALCULATE(SUM('Table'[Value]))
return IF(V>R[R Value],"#FD625E",
            IF(V>[A Value]&&V<R[R Value],"#F2C80F",
                IF(V>G[G Value]&&V<R[R Value],"#01B8AA")))

        You need to give your IF condition with a range, if all IF condition is >, the dax could only show one of the IF conditions.

         

        Regards,

        Daniel He

  • StidifordN's avatar
    StidifordN
    Helper III
    7 years ago

    Fantastic solution.  I was able to accomplish colour coded 'ticks' :smileylol:

     

  • Rickmaurinus's avatar
    Rickmaurinus
    Helper V
    5 years ago

    The measure way works miracles. Another approach could be to use some custom conditional formatting using a measure. In that way you can format the numbers in any way you like. For anyone still looking for an alternative:  https://www.youtube.com/watch?v=K-uhWhl9Grs

     

    --------------------------------------------------

    @ me in replies or I'll lose your thread

     

    Master Power Query M? -> https://powerquery.how

    Read in-depth articles? -> BI Gorilla

    Youtube Channel: BI Gorilla

     

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