Forum Discussion

Anonymous

Not applicable

2 years ago

Solved

Conditional formatting

Hello,

Is it possible to apply conditional formatting based on (value in card is equal to or less than or greater than measure value)

Example: "Measure" value is dyanmic (usually 1-100)

card value = achievement of agent

Expected outcome: if achivement of agent is less than "Measure" value then the background of the value should be RED

Below was achieved using conditional formatting in excel with ()

Need in power bi matrix visual

Dangar332
2 years ago
hi, Anonymous
follow below step
make mesure for outcome
formatting =
var target = 500
var clo = SUMX(condifo,condifo[closedpoint])
var a = DIVIDE(clo,target)*31
var b =7
var final = DIVIDE(a,b)
return final

create mesure for rules for conditional formatting
rules = SWITCH(TRUE(),
condifo[formatting]<0.5,"#FF0000",
condifo[formatting]<0.7,"#FFFF00",
condifo[formatting]<0.9,"#FFA500",
condifo[formatting]<1,"#90EE90",
condifo[formatting]>1.2,"#00FF00")

create table visuals or you can change if another needed

go to formatting measure > select conditional formatting > select background color

select feild value

press ok
you get these

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

6 Replies

Dangar332
Resident Rockstar
2 years ago
hi, Anonymous
select your matrix visual got to visuals formatting apne and select cell element and enble background color like below

select fx it open conditional formatting pane here change format style to rule , and what field should we base this on select your your column using your table name.
and put your rules in condition like below
after these your matrix look like
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
- Anonymous
  Not applicable
  2 years ago
  Hi Dangar332
  
  Thank you for your response. I tried the option you provided & it works well for static rule.
  
  However, in our scenario, this rule values needs to be changed everyday as the target for the day changes as **Target = 100/31 days multiplied by today***.
  
  Please advise a best way to achieve this 🙂
  
  Kudos
  - Dangar332
    Resident Rockstar
    2 years ago
    hi, Anonymous
    provide sample data and what you want.

Anonymous

Not applicable

Below is what we achieved in Excel:

closed points is SUM of each agent's achievement
- Then conditionally formatted with rule "closed points divided by target multiplied by no.of days in the month divided by today = x%")
  - if x% is equal to or less than 50% then background color = Red
  - if x% is between 50-90 then yellow and so on)

Agent Name	Closed Points	formula here	Outcome		Target assumed	500
A	120	=(B2/500)*31/DAY(TODAY())	1.06		days in month	31
B	80	=(B3/500)*31/DAY(TODAY())	0.70		today value	7	07/10/2023
C	49	=(B4/500)*31/DAY(TODAY())	0.434
D	102	=(B5/500)*31/DAY(TODAY())	0.9
					Conditional formatting rules based on value
				outcome<	0.5	Red	Stop if true
				outcome<	0.7	Yellow	Stop if true
				outcome<	0.9	Orange	Stop if true
				outcome<	1	Light Green	Stop if true
				outcome>	1.2	Green	Stop if true

Dangar332
Resident Rockstar
2 years ago
hi, Anonymous
follow below step
make mesure for outcome
formatting =
var target = 500
var clo = SUMX(condifo,condifo[closedpoint])
var a = DIVIDE(clo,target)*31
var b =7
var final = DIVIDE(a,b)
return final

create mesure for rules for conditional formatting
rules = SWITCH(TRUE(),
condifo[formatting]<0.5,"#FF0000",
condifo[formatting]<0.7,"#FFFF00",
condifo[formatting]<0.9,"#FFA500",
condifo[formatting]<1,"#90EE90",
condifo[formatting]>1.2,"#00FF00")

create table visuals or you can change if another needed

go to formatting measure > select conditional formatting > select background color

select feild value

press ok
you get these

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly. Appreciate your kudos.
- Anonymous
  Not applicable
  2 years ago
  Appreciate your support Dangar332