Conditionally Formatting text values based on multiple associated numbers

[Anonymous]

I'm trying to figure out a way to format text values based on an associated number, but one text value could be associated with mutliple numbers. Is it possible to write a formula to take the AVG of associated numbers to format text values.

An example would be for part1, it shows up twice in the table with values 3 & 1 so it has an avg value of 2, part2 is has 2 & 1 so its avg is 1.5, and part 3 has an avg of 3. What would be the best way to take the average of numbers in column 4 to conditionally format values in column 3. I would like to do this for every column in a bigger data set so I want to avoid making new columns to store associated averages. 

[lbendlin · Accepted solution]

You can do this entirely in the Power BI user interface.  Use an implicit measure to show the average, and the same to create the conditional formatting.  See attached.

[lbendlin · Accepted solution]

You can do this entirely in the Power BI user interface.  Use an implicit measure to show the average, and the same to create the conditional formatting.  See attached.

[Anonymous]

The problem is if I want to conditionally format the vals, it doesn't take the average of the sets of values since the part column is different for val4. It takes the average value seperately for each instance of val4.

In this case the columns are being formatted by the average value of the hardiness cloumn. It shows tree as having two seperate avg values and seed as having seperate avg values. How could I get all similar values to have the same average

[lbendlin]

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

[Anonymous]

So I realized that it is just taking the average of the hardiness in a row, rather than the hardiness associated with the values. I want it to take the average value of the hardiness for all entries with value "maple" which would be 5 and format the cells with the value maple as green. Then all the values "bush" should have an average of 7.5 since the azelea bush has a hardiness of 6 and the holly bush has a hardiness of 9. I want it to take the average of all the similar values in a column, not just take the average of the exact same entries. Currently since there are two entries of "golf" "grass" "seed" it is taking the average of those 2 entries. I want it to take the average of every entry with "golf" (6), and seperately all the entries with "grass" (5.66), and "seed" (4.14) and format accordingly.

all columns are conditionally formated based on the hardiness row

expected results (yellow 9-6.5) (green 6.49-3.51) (blue 3.5-1)

data

Species	Plant	Type	Quantity	Hardiness
holly	bush	leaf	2	9
maple	tree	leaf	3	9
iris	flower	bulb	3	7
azelea	bush	leaf	10	6
oak	sapling	seed	6	6
wild	grass	seed	8	5
ginko	tree	leaf	5	4
golf	grass	seed	3	3
oak	tree	seed	3	3
rose	flower	bulb	6	3
ginko	tree	seed	4	2
maple	sapling	seed	2	1
golf	grass	seed	3	9

[lbendlin]

You have two rows for golf-grass-seed .  Please indicate what the hardiness should be - 3 or 9 ?

Does the Quantity have any meaning?  If so - please describe it.

[Anonymous]

Use 3 for the golf grass seed. The quantity is just used for a weighted average calculation seperate from this. 

[lbendlin · Accepted solution]

@Anonymous Something like this?

see attached.

[Anonymous]

Yes! That looks right! Sorry for not giving enough enough information initially. Thank you so much