Field Conditional Formatting Table with multiple measures

[50ryk]

I have a table that dispalys the sales numbers for each day of the week of different locations.

LOCATION	MONDAY	TUESDAY	WEDNESDAY
CALIFORNIA	651	802	855
TEXAS	625	660	786

Each sales number for a day is calculated by a Measure. I have a Measure for Monday, Tuesday, Wednesday, etc..

Each location has different sales targets, ie California's target sales number is 800 and Texas' sales target is 650. I have a table with the sales target for each location. The table has a one to many relationship with the sales table that has a cross-filter in both directions.

I would like to have conditional formatting on the sales numbers based if they achieved their target or not. I have created another measure with a switch function based on each day.

CONDITIONAL_FORMATTING = 

  VAR SALESTARGET = SELECTEDVALUE(SALESTARGET_TABLE[TARGET])

  RETURN

  SWITCH(

      TRUE(),

      [MONDAY] >= SALESTARGET, "GREEN",
      [MONDAY] < SALESTARGET, "RED"

)

That works for individual days but I would rather not have 7 separate conditional formatting measures for each day. I tried substituting SELECTEDMEASURE() for the actual measure but this does not work. Is there anyway I can accomplish this?

[Alex_Sawdo · Accepted solution]

Instead of using a table for this data, it may be easier to use a matrix instead with a single measure rather than individual measures for each day and hand some of the filtering off to the visual level rather than the measure level. You would put the day of week in the column field and the location in the row field, then your measure in the values field. Then, you can implement conditional formatting on a single measure based on the SELECTEDVALUE of the row and column through the existing relationship. 

[wardy912]

Hi @50ryk 

 You could use a calculation group. That will allow you to use one measure for all outcomes. You could use this instead of a separate measure for each day as well.

The following videos will give you what you need and more

Unlock the Full Potential of CALCULATION GROUPS in Power BI Desktop | Oct 2023 Power BI Update - You...

How to Conditional Format CALCULATION GROUP Columns in Power BI

Please give a thumbs up and mark as solved if this helps, thanks!