Forum Discussion

coyote_ptm's avatar
coyote_ptm
Frequent Visitor
5 years ago

Conditional formatting on dimension attribute

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?

 

Need Help

2 Replies

    • coyote_ptm's avatar
      coyote_ptm
      Frequent Visitor
      4 years ago

      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.