Change background colour of card based on multiple calculated measure

[Anonymous]

Hi All,

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

What I have:

Employee Name	Gender	City_Index	Country_Index
Zen	Male	-87	50
Kim	Male	0	36
Sharyn	Female	-45	22
Lexi	Female	-66	30
Maria	Female	-91	1
John	Male	-70	11
Shan	Male	-23	0

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

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.

[v-shex-msft · Accepted solution]

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" )
        )
    )

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

[amitchandak]

@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]

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