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
Anonymous
Not applicable

Change background colour of card based on multiple calculated measure

Hi All,

 

I am trying to change the background color on a card based on the calculated measure.

 

What I have:

 

Employee NameGenderCity_IndexCountry_Index
ZenMale-8750
KimMale036
SharynFemale-4522
LexiFemale-6630
MariaFemale-911
JohnMale-7011
ShanMale-230

 

Calculated Average Measure:
Average_City_Index = CALCULATE(AVERAGE(Table[City_Index), FILTER(Table,Table[City_Index] <> 0 ))
Average_Country_Index = CALCULATE(AVERAGE(Table[Country_Index), FILTER(Table,Table[Country_Index] <> 0 ))

Measure to change color:

 

 

 

Condition_Measure = MAXX(Table,
    IF([Average_City_Index] >= -50 && [Average_City_Index] <= -1, 1,
        IF([Average_City_Index] >= -75 && [Average_City_Index] <= -51, 2,
            IF([Average_City_Index] >= -100 && [Average_City_Index] <= -76, 3, 4))))

 

 

 

 

Background Condition

screen1.PNG

 

Result:

When I create a card to get count of employee and then filter it based on gender in the filter section. The value I get is 4 which is right because there are 4 males but the background color is light blue instead of yellow, the average of city_index for male is -60.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

I think you can use 'field value' mode and direct to return color code and use on color formatting, it should more simple than rule mode.

Use conditional formatting in tables 

BTW, maxx function seems not needed in your formula, after I remove this and modify your formula, it works well on my side.

 

Color = 
VAR avgCity =
    CALCULATE ( AVERAGE ( T1[City_Index] ), T1[City_Index] <> 0 )
RETURN
    IF (
        avgCity >= -50
            && avgCity <= -1,
        "Green",
        IF (
            avgCity >= -75
                && avgCity <= -51,
            "Yellow",
            IF ( avgCity >= -100 && avgCity <= -76, "Red", "light Blue" )
        )
    )

 

7.png

8.png

If above not helps, please share more detailed information to help us clarify your scenario.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

I think you can use 'field value' mode and direct to return color code and use on color formatting, it should more simple than rule mode.

Use conditional formatting in tables 

BTW, maxx function seems not needed in your formula, after I remove this and modify your formula, it works well on my side.

 

Color = 
VAR avgCity =
    CALCULATE ( AVERAGE ( T1[City_Index] ), T1[City_Index] <> 0 )
RETURN
    IF (
        avgCity >= -50
            && avgCity <= -1,
        "Green",
        IF (
            avgCity >= -75
                && avgCity <= -51,
            "Yellow",
            IF ( avgCity >= -100 && avgCity <= -76, "Red", "light Blue" )
        )
    )

 

7.png

8.png

If above not helps, please share more detailed information to help us clarify your scenario.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@Anonymous , Not very clear. You can create a color measure and use that in conditional formatting by choosing "Field value" option

Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")
Color Year = if(FIRSTNONBLANK(Table[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))

Color  = if(FIRSTNONBLANK(Table[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170 
,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))
Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
// Add more conditions
"red"
)

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692

 

lbendlin
Super User
Super User

You know you can provide colors directly in your measure and then use field values instead of rules to drive the formatting, right?

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

Top Solution Authors