Apply conditional formatting background color to subgroups in a matrix

[RobertoMonzon]

I am working with my "data" table in power bi, I have a column called "department" where I have the department where my employees work, another called "worker" where I have the name of my employees, "CH" where I have the name of the stages of a project, "time" is the time it takes for each stage. I created a measure to calculate the average per department and a measure to change the color.

This is how my matrix looks:

The code for the average:

avg = 
CALCULATE(
    AVERAGEX(
        SUMMARIZE(
            data, 
            data[Time], 
            "average", AVERAGE(data[Time])
        ),
        [average]
    )
)

The code for the color:

My color = 
VAR __minvalue=MINX(ALLSELECTED(data[department]),[avg])
VAR __maxvalue=MAXX(ALLSELECTED(data[department]),[avg])
VAR __sumvalue=data[avg]
RETURN
SWITCH(
    TRUE(),
     __sumvalue==__maxvalue,"Green",
    __sumvalue==__minvalue,"Red",
    "orange"
)

The conditional formating:

The problem is that when I display the subgroups it changes color and the main groups lose the conditional

I'm getting desperate I hope you can help me.I would like the subgroups to also change or at least for the main groups to not lose their color.

Thanks in advance

[v-kaiyue-msft · Accepted solution]

Hi @RobertoMonzon ,

Your idea is correct, to have both parent and child groups of the matrix show the number of conditions, just change apply to to values and totals.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RobertoMonzon]

I make this code that almost work but the problem is that the principal ground still changing the conditional format

col = 
VAR __minvalue=MINX(ALLSELECTED(data[department]),[avg])
VAR __maxvalue=MAXX(ALLSELECTED(data[department]),[avg])
VAR __minivalue=MINX(ALLSELECTED(data[worker]),[avg])
VAR __maxivalue=MAXX(ALLSELECTED(data[worker]),[avg])
VAR __sumvalue=data[avg]
RETURN
IF(NOT(ISINSCOPE('data'[worker])),
SWITCH(
    TRUE(),
     __sumvalue==__maxvalue,"Green",
    __sumvalue==__minvalue,"Red",
    "orange"
),
SWITCH(
    TRUE(),
     __sumvalue==__maxivalue,"Green",
    __sumvalue==__minivalue,"Red",
    "Orange"
)

)