Conditional formatting of a measure based on another measure

[byr10112]

I'm trying to get a matrix conditionally formatted based on the results of a measure. The matrix currently includes the percent of available sources used by the employees, which is a measure. For example, if there are 100 sources available to be used and an employee used 60 of those sources, the source utilization of the employee is 60%. The source utilization is a measure, where I divide the Number of Sources Used by the Number of Sources Available. The employee names populate the rows and the Months are the columns in the matrix. 

I also have a separate measure called target, which changes based on the month. For example, the target utilization of sources is 80%, meaning we expect the employees to use at least 80 sources from the available 100. The target ranges from 40 in January to 90 in July. The target measure is calculated as Target Sources/  Number of Sources available.

I have two separate tables, one for the actual numbers and the other for the target. Targets are company-wide targets and do not change based on the empoyee. The employee util table has Employee, Month, Sources Used and Sources Available. The target table has  Month, Sources Targeted and Sources Available info. 

I would like to color code my matrix values based on whether the employee's source usage meets the target or not. If the employee's source util is less than the target util, the cell should be filled in Red, and Green if the source util is greater than or equal to target util.  

Is there a way for me to do this? Below is the sample data:

Employee Table:

Employee	Month	Sources Used	Sources Available
Peter Jackson	January	85	100
Adam Devine	January	74	100
John Smith	March	65	95
Peter Lucas	June	67	108

Measure: Employee Util = SUM(Sources Used) / SUM(Sources Available)

Target Table:

Month	Sources Available	Target
January	100	80
February	109	84
March	95	71
April	91	67

Measure: Target Util = SUM(Target)/ SUM(Sources Available)

Matrix:

Employee	January	February	March	April	May
Peter Jackson	.85	.90	.79	.55	.78
Adam Devine	.74	.87	.55	.87	.94
John Smith	.64	.57	.68	.34	1.0
Peter Lucas	.67	.64	.94	.46	.56

Depending on the target, the cell should be either red or green as mentioned above. 

[Anonymous · Accepted solution]

Have you tried the built-in conditional formatting feature in Power BI?

You can conditionally format one measure by a different measure as well.  So you could create a measure that checks to see if the Sources Used % > Target Used % and then color by that.

[Greg_Deckler]

@byr10112 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[byr10112]

I added some sample data