Forum Discussion

aasamassa's avatar
aasamassa
Advocate I
1 year ago
Solved

Conditional Formatting for a matrix based on another table

Hello,   I have a matrix that shows the number of employees audited per production lines per month.   I would like to add a conditional format that shows if the target of audits was met for each ...
Need Help
  • Syndicate_Admin's avatar
    Syndicate_Admin
    1 year ago

    Hello

    I hope to see the question understood. You have 2 tables and want to reference the second table (Objectives) as conditional formatting in your parent table. I hope this helps you, in any case if not, tell me how it could help you oh you like it if that's what you asked.

    Conditional formatting, using the second table as ref.:

    Solution:

    1. Create a table with the following formula:

    Calendar =

    ADDCOLUMNS (

    CALENDAR (DATE(2025, 1, 1), DATE(2025, 12, 31)),

    "MesAño", FORMAT([Date], "MMM-YYYY"),

    "MonthYearOrder", YEAR([Date]) * 100 + MONTH([Date])

    )

    2. Relate the 2 tables you mention to the new Calendar table:

    • PK, Calendar (Date) and Audit (Date)
    • PK, Audit (LineProduction) and AuditObjective = (MonthlyObjective)

    3. Create a Matrix table with the following formulas:

    • In the Rows section, select = LineaProduction (Audit table)
    • In the Column, Select the Calendar = MonthYear
    • Under Values, select = Total Audit
      • Formula for Total Audits = COUNTROWS

    4. Conditional Formatting:

    Select the matrix table and in the Values section "Total Audits" = Use the Background color option and select MeetObjective.

    I show you the values you will put in:

    This is the formula you'll need to create first for MeetObjective.

    (This formula looks up the values in the second table to give it conditional formatting)

    MeetObjective =

    VAR line = SELECTEDVALUE(Audits[Production Line])

    VAR objective = LOOKUPVALUE(

    ObjectivesAudit[MonthlyObjective],

    ObjectivesAudit[Production Line],

    line

    )

    VAR Audits = [Total Audits]

    RETURN

    IF(Audits >= objective, 1, 0)

    ________________________________________________________

    Data model of the first table: Audits

    Data model of the second table: ObjectivesAudit

    Luck!

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
1 year ago

I hope I have understood your question correctly. You mention that you have two tables, and you want to use the second one as a reference for applying conditional formatting. Here are the steps to achieve it. If your case is different, please tell me and I will gladly help you adjust it.
And if this solved your doubt, I would love for you to "like" the answer

Conditional formatting with reference to the second table as objectives

Solution:

1. Create a table with the following formula:

Calendar =

ADDCOLUMNS (

CALENDAR (DATE(2025, 1, 1), DATE(2025, 12, 31)),

"MesAño", FORMAT([Date], "MMM-YYYY"),

"MonthYearOrder", YEAR([Date]) * 100 + MONTH([Date]))

2. Relate the 2 tables you mention to the new Calendar table:

  • PK, Calendar (Date) and Audit (Date)
  • PK, Audit (LineaPrioduccion) and ObjectiveAudit (LineProduction)

3. Crea a Matrix table with the following formulas:

  • In the Rows= Production Line section (Audits)
  • In the Column, Select the Calendar = MonthYear (New Calendar)
  • Under Values, select = Total Audits (New Formula)
    • Create this formula, Total Audits = COUNTROWS(Audits)

4. Conditional Formatting:

Select the matrix table and in the Values= section, use the Backgroung option and select MeetTarget. I show you what values you will put on yourself

-> Create this formula first

MeetObjective =

VAR line = SELECTEDVALUE(Audits[Production Line])

VAR objective = LOOKUPVALUE(

ObjectivesAudit[MonthlyObjective],

ObjectivesAudit[Production Line],

line

)

VAR Audits = [Total Audits]

RETURN

IF(Audits >= objective, 1, 0)

___________

Table Template1 Audit

Table Model2 ObjectiveAudit

Luck!