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

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;

1 ACCEPTED SOLUTION
Microsoft Employee

Hi @rax99 ,

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

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.
5 REPLIES 5
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.

Helper III

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

Microsoft Employee

Hi @rax99 ,

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

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.
Helper V

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.

Microsoft Employee

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

#### Power BI Monthly Update - July 2024

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

Top Solution Authors
Top Kudoed Authors