Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rax99
Helper V
Helper V

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;

 

conditional fomat color.JPG

1 ACCEPTED SOLUTION
v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @rax99 ,

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

Create three parameters:

1.PNG

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:

1.PNG

Now you could change the parameters for your colored visual.

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Rickmaurinus
Helper V
Helper V

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.

StidifordN
Helper III
Helper III

Fantastic solution.  I was able to accomplish colour coded 'ticks' Smiley LOL

 

Capture123333.JPG

v-danhe-msft
Microsoft Employee
Microsoft Employee

Hi @rax99 ,

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

Create three parameters:

1.PNG

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:

1.PNG

Now you could change the parameters for your colored visual.

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

Hi @rax99 ,

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors