Conditional formatting on dimension attribute

[coyote_ptm]

I have seen similar posts regarding this, but they don't seem to be usable in my scenario. I'd like to format a measure with different background colors based on an attribute from a dimension table. Different colors if measure is above or below the dimension.

LaborHours[Charge Ability PCT] is the measure and Employees[TargetRatio] is the attribute to base the formatting on.

// the join between tables
LaborHours[Employee] = Employees[Employee]

// the measure
Charge Ability PCT = IF( SUM( LaborHours[Chargeable Hours] ) == 0, 0, 
                         SUM( LaborHours[Billable Hours] ) / SUM( LaborHours[Chargeable Hours] ) )

I have used a separate "color" field before when using a summary table, but the dimensional attribute is included in the summary table. The summary table will go away as soon as I get this issue resolved.

TargetReached = IF( ChargeAbility[ChargeabilityPCT] == 0, "#FFFFF", IF( ChargeAbility[ChargeabilityPCT] < ChargeAbility[ Target Ratio], "#FF7C80" , "#A9D08E" ) )

If I try the same formula in the LaborHours table referencing the Employees table, I get an error - can't use employees in this expression.

Is there a way to work around this?

[amitchandak]

@coyote_ptm , You need to use related, relatedtable of MAXX to filter and bring that in a new calculated column

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[coyote_ptm]

Following up.  I brought the Target Ratio into the LaborHours table.

New column:

Target Ratio = LOOKUPVALUE( Employees[ Target Ratio], Employees[Employee], LaborHours[Employee] )

Created Average measure of target ratio:

Target Ratio Measure = AVERAGE( LaborHours[Target Ratio] )/100

This was the issue for me - I didn't create this as a measure and it didn't work.  It would always be 1 color because it was comparing to each row in the fact table, not the sum of the fact table.

Met Charge Color = IF( LaborHours[Charge Ability PCT]  == 0, "#FFFFF", IF( LaborHours[Charge Ability PCT]  <  LaborHours[Target Ratio Measure]  , "#FF7C80" , "#A9D08E" ) )

It's too bad it couldn't just use the ratio from the employee table without adding to labor hours table, but this works for now.