cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

Accepted Solutions
Highlighted
Community Support
Community Support

Hi @nikeshv,

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
Highlighted
Super User II
Super User II

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

Highlighted
Super User IV
Super User IV

@nikeshv , 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-num...
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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Hi @nikeshv,

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors