Conditional Formatting to color values not working properly in visual with drill-up timeline

[dirkkoch]

I am experiencing some issues when I use conditional formatting of values in combination with the drill-up function related to a timeline. 

I have a date slicer in which the user can select the desired timeframe. The date slicer is linked to a line and stacked column chart, which includes the date on the x-axis (date hierarchy: day, month, year), the actual value on the y-axis (aggregation: average) and the threshold value as line on the y-axis (aggregation: average). See examplary table below. 

The conditional formatting is supposed to color the actual value, if it is below the threshold value (-> green) or above the threshold value (-> red).  This works fine if the date on the x-axis is set to day. But whenever I use the drill-up function in the visual and chang e the date to month or year, the conditional formatting is not shown correctly (see example screenshot August 2023 -> 6,42 shown in green; should be red since above 5,48). I also tried to modify the following formula used for conditional formatting to average values etc., but nothing seemed to solve the issue. Can anybody offer helpful support here or any other suggestions? Thank you in advance!

Dax formula conditional formatting = If(isblank(table[Actual Value]), Blank(), if(table[Actual Value] > table[Threshold Value], "red",

"green"))

Date                            Actual Value                   Threshold Value          Colour Actual Value (Conditional Formatting)
28.09.2023                  5,23                                 5,48                             green
29.09.2023                  6,34                                 5,48                             red
30.09.2023                  5,12                                 5,48                             green
01.10.2023                  5,56                                 5,48                             red
02.10.2023                  5,33                                 5,48                             green

.....                                ......                                  ......                               .......

Date slicer:

Date filter

Date: day shown correctly

Daily Visual

Date: month or year not shown correctly

Drill-Up Monthly Visual

[Uzi2019]

Hi @dirkkoch 
Please follow this video. I hope this video helps you to solve conditional formatting issue.

https://www.youtube.com/watch?v=lpdQmcUZATs

Please give kudos and accept as solution!
Thanks.

[dirkkoch · Accepted solution]

@Uzi2019 thanks, but that is a very general video for conditional formatting, which does not solve the identified problem. My issue is related to the drill-up function in the date hierarchy/average aggregation and is rather specific.