i am trying to add conditional background color in table matrix

[Kancham123]

Hi ,

i am trying to create conditional background color in a table matrix.

i am looking condition here is if total avg for each part number < Each month Cost([Total Cost/Tool Month]), then Red color else green.

for an example with below image
issue now  i am getting everything red ,  Result Expecting it actual only red at Mar month only because its more than avg 5.16

used calultaion details 
BackgroundColor = IF(
[Total Cost/Tool Month]> [AVG with Year month],
"#FF0000", // Red color for values greater than average
"#00FF00" // Green color for values less than or equal to average
)

AVG with Year month =

AVERAGEX(

    KEEPFILTERS(VALUES('Buzzsaw Metric_Tool Months'[Year-Month])),

    CALCULATE([Total Cost/Tool Month])

)

cost measure Total Cost/Tool Month = DIVIDE([Total Cost_Buzzsaw],[Tool Months])


Year-Month = 'Buzzsaw Metric_Tool Months'[YEAR]& " " & FORMAT('Buzzsaw Metric_Tool Months'[Date],"MMM")

Tool Months = COUNT('Buzzsaw Metric_Tool Months'[MONTH])

Total Cost_Buzzsaw = [Install Cost] + [Warranty Std Cost]

Matrix Tablue used fileds 

[Kancham123]

i have got the solution from outside, so i did not try this solution but anyway i am consider this solution can be helful.  thank you

[v-priyankata]

Hi @Kancham123 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[antfr99 · Accepted solution]

Hi @Kancham123 

I suggest the following measure to get your average :

AVG with Year month = 
CALCULATE(
    DIVIDE(SUM(Data[Cost]), DISTINCTCOUNT(Data[Months])),
    ALLSELECTED(Data[Months]) 
)

Then this measure for your colour :

BackgroundColor = 
IF(
    SUM(Data[Cost]) > [AVG with Year month],
    "Red",
    "Green"
)

Then place this in values :

Total Cost_Buzzsaw = SUM(Data[Cost])

from which you can click on the drop down on this value and apply conditional formatting :

ou should then get this which I think follows the logic you require ( comparing in this case to the filtered average of 15, not the total average of 65 as 65 below is not filtered > its the average for all months  )

below is the data sample :

Hope this helps
Antonio