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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
iknowganesh
Frequent Visitor

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 (iknowganesh_2-1696412469516.png)

iknowganesh_0-1696412389581.png

 

Need in power bi matrix visual 

iknowganesh_3-1696412545806.png

 

 

1 ACCEPTED SOLUTION

hi, @iknowganesh 
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
Dangar332_0-1696707373005.png

 

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

Dangar332_1-1696707543423.png

 

select feild value

Dangar332_2-1696707644813.png

 

press ok 
you get these

Dangar332_3-1696707708414.png

 

 

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

 

View solution in original post

6 REPLIES 6
iknowganesh
Frequent Visitor

Hi @Dangar332,

 

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)

iknowganesh_0-1696655224663.png

Agent NameClosed Points formula hereOutcome Target assumed500 
A120=(B2/500)*31/DAY(TODAY())1.06 days in month31 
B80=(B3/500)*31/DAY(TODAY())0.70 today value707/10/2023
C49=(B4/500)*31/DAY(TODAY())0.434    
D102=(B5/500)*31/DAY(TODAY())0.9    
     Conditional formatting rules based on value 
    outcome<0.5RedStop if true
    outcome<0.7YellowStop if true
    outcome<0.9OrangeStop if true
    outcome<1Light GreenStop if true
    outcome>1.2GreenStop if true
        

hi, @iknowganesh 
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
Dangar332_0-1696707373005.png

 

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

Dangar332_1-1696707543423.png

 

select feild value

Dangar332_2-1696707644813.png

 

press ok 
you get these

Dangar332_3-1696707708414.png

 

 

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

 

Appreciate your support @Dangar332 

Dangar332
Super User
Super User

hi, @iknowganesh 

 select your matrix visual got to visuals formatting apne and select cell element and enble background color like below

Dangar332_0-1696413901051.png

 

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

Dangar332_1-1696413991829.png

after these your matrix look like 

Dangar332_2-1696414027922.png

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

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

hi, @iknowganesh 
provide sample data and what you want.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.