Conditional formatting logic keeps reverting back to percent/number

[leolapa_br]

I have the following visual where I set the amounts in % to display in red whenever they come in negative, as highlighted on the screenshot below:

When there is no filter applied the formatting works just fine, however whenever I apply a filter the formatting is thrown out of whack, showing instances of positive numbers in red and negative numbers in default color, as shown below:

I then went back to the conditional formatting setup to check what's going on, and that's when things got interesting...

Every time I open the conditional formatting setup window it comes up by default with the following logic:

It's mixing up Percent and Number on each side of the logic, and a logic that doesn't make sense since no number can be larger than or equal to 0% and less than 0 (negative) at the same time...

So I proceed on fixing that by changing the left side of the logic to Number and erasing the 0 so it picks up whatever number is larger than or equal to the minimum as long as it's negative (thus the less than 0 Number on the right side of the logic):

I then hit OK but the issue persists, since every time I open back the conditional formatting setup window Power BI keeps reverting the rules back to the default screwed up logic it was before!

The 'What field should we base this on?' looks fine since it's referring to the respective measure used for this field. Not sure if the reason this is happening is due to the fact that the measure is currently formatted as a percentage? But that should not be the case... I'm really stuck on this one, almost thinking this is a bug within Power BI.

P.S.:
I've also tried to setup the logic with Percent on both sides, with either -100% or Min on the left side, but Power BI won't accept it asking me to check the logic, which by the way looks OK (at least to me unless I'm missing something here...):

[Bibiano_Geraldo]

Hi @leolapa_br ,

The behavior you're observing with the conditional formatting is expected, but let’s analyze both visuals together to better understand what’s happening.

In your second visual (the filtered one), you’ll notice that the conditional formatting remains consistent: the red areas stay red, and the green areas stay green as first visual (not filtered), regardless of whether the values are positive or negative.

What does this mean?
It indicates that the measure you're using for conditional formatting, "ticket médio ASA %," likely contains a function that is overriding the filters applied in the visual. For instance, you might be using the ALL function or another function that removes filters within your measure.

To fix this:

1. Review your measure.
   Check if functions like ALL, REMOVEFILTERS, or CALCULATE are used in a way that ignores your slicer or visual filters.
2. Adjust the measure logic.
   Modify the measure so it respects the filters applied in the visual. For example, avoid ALL unless it’s absolutely necessary, or use KEEPFILTERS to ensure the slicer filters are honored.

If my theory isn’t correct, I recommend trying the solution provided by @Jai-Rathinavel , as it could address the issue from another angle.

[leolapa_br]

I really apreciate your time on this one @Bibiano_Geraldo.

It's funny that by the time you were writing your answer I was replying to @Jai-Rathinavel's suggestion and mentioning the same finding you had, which is the one that concerns the fact that the formatting stays consistent across months whether or not a filter is applied.

And I agree that the use of ALL/REMOVEFILTERS, etc. on any of the measures that lead up to 'Ticket médio ASA %' might be the culprit on this one.

Below is a compilation of all measures that lead up to 'Ticket médio ASA %' being 'fVendas' the facts table containing all sales-related data and 'dCalendario' being the dates/calendar table:

Ticket médio ASA % = -- It divides...
DIVIDE (
    [Ticket médio ASA], -- ... the difference between this year's and last year's measure.
    [Ticket médio AA], -- ... by the prior year's measure.
    0
)

Ticket médio ASA = 
VAR ValorPeriodoAtual = [Ticket médio] -- This year's measure.
VAR ValorPeriodoAnterior = [Ticket médio AA] -- Last year's measure.
VAR Resultado =
    IF (
        NOT ISBLANK ( ValorPeriodoAtual ) && NOT ISBLANK ( ValorPeriodoAnterior ),
        ValorPeriodoAtual - ValorPeriodoAnterior
    )
RETURN
    Resultado

Ticket médio AA = -- Last year's measure.
IF (
    [MostrarValorParaDatas],
    CALCULATE (
        [Ticket médio],
        CALCULATETABLE (
            DATEADD ( dCalendario[Data], -1, YEAR ),
            dCalendario[DataComTransações] = TRUE
        )
    )
)

MostrarValorParaDatas = -- Check whether each respective date had sales.
VAR UltimaDataComDados =
    CALCULATE (
        MAXX ( { MAX ( 'fVendas'[Mês/Ano] ), MAX ( 'fVendas'[Data] ) }, [Value] ),
        REMOVEFILTERS ()
    )
VAR PrimeiraDataVisivel =
    MIN ( 'dCalendario'[Data] )
VAR Resultado =
    PrimeiraDataVisivel <= UltimaDataComDados
RETURN
    Resultado

Ticket médio = -- It divides...
DIVIDE(
    [Faturamento], -- ... this year's sales dollars.
    [Quantidade de vendas], -- ... by this year's sales volumes.
    0
)

Quantidade de vendas = -- This year's sales volumes.
VAR Tabela_Prov = SUMMARIZE(
    fVendas,
    fVendas[Mês/Ano],
    "NF Cont", DISTINCTCOUNTNOBLANK(fVendas[Nota Fiscal])
)
RETURN
SUMX(
    Tabela_Prov,
    [Contagem de vendas]
)

Contagem de vendas = DISTINCTCOUNTNOBLANK(fVendas[Nota Fiscal])

Faturamento = -- This year's sales dollars.
SUMX( 
    fVendas,
    (fVendas[Quantidade] * fVendas[Preço Unitário]) - fVendas[Desconto] + fVendas[Acréscimo]
)

 The only measure that uses REMOVEFILTERS is [MostrarValorParaDatas] which is a hidden measure within the 'dCalendar' table used just to check whether each respective date had sales.

I actually removed that measure within [Ticket médio AA] by getting rid of the IF statement but the problem persisted, so it doesn't look like the problem resides there.

[Bibiano_Geraldo]

@leolapa_br , The variação % vs ano anterior is the same with Ticket médio ASA % measure? if no, use variação % vs ano anterior  measure as condition to your rules.

[leolapa_br]

Hi @Bibiano_Geraldo, they're both the same thing, sorry for not mentioning it before. I had the measure renamed just to make it more readable for the end users.

[Bibiano_Geraldo]

Hi @leolapa_br, it's supposed to work, just to make sure, copy the visual and transform to matrix and add the conditional format to see if the problem persists. If yes, consider to share no sensitive sample, just to interact with the report and troubleshoot this issue

[leolapa_br]

Hi @Bibiano_Geraldo, I've done as you suggested and created a sanitized PBI in English so I can share. Please find the OneDrive link below:

Sales_Data.pbix

While doing that I found out that the conditional formatting issue only persists when the filtering is done via a treemap visual featured on the dashboard next to the column/line chart that I've been trying to fix. If the filtering is done via a slicer then the conditional formatting occurs without a problem. The sequence of screenshots below depicts what I mean...

This is the dashboard mock up with no filters applied:

Now the dashboard mock up filtered to a single store via slicer. No conditional formatting issues on neither the matrix nor the chart:

And finally the dashboard mock up filtered to a single store via the treemap visual. The matrix still presents no conditional formatting issues, but the chart's conditional formatting now is wrong:

What puzzles me is why the problem arises when filtering with the treemap, and why the problem only shows up at the chart while the matrix is still OK. Questions I don't have an answer for!

[Bibiano_Geraldo · Accepted solution]

Hi @leolapa_br , look how its easy when we interact with a dashboard, what you need is just edit interactions between any visual including treemap to filter your column chart.

Select treemap visual, go to format, click on edit interactions and in your column chart choose filter as shown bellow:

When i click in treemap value to filter, its now showing correct values.

[leolapa_br]

I really appreciate your help on this one @Bibiano_Geraldo!

When I realized that it had something to do with the fact that I am filtering via another visual I suspected the solution to the problem would be something easy but in an area I'm not entirely familiar with, and that's the case with editing interactions. Lesson learned!

[Bibiano_Geraldo]

Happy it worked, @leolapa_br !

[Jai-Rathinavel]

@leolapa_br  Setting rules sometimes becomes a pain. So, Create a measure for conditional formatting like below and set it as field value in conditional formatting.

Label Format = SWITCH(TRUE(),
                      [Ticket medio ASA %] < 0 , "Red",
                      [Ticket medio ASA %] > 0 , "Green") 

Did I answer your question? If yes, please mark my post as a solution.

Thanks,

Jai

[leolapa_br]

Thanks for taking the time @Jai-Rathinavel.

I created the measure as directed and applied to the conditional formatting setup but unfortunately the issue persisted.

But that made me realize that the formatting is somehow 'stuck' to each respective month regardless of whether or not a filter is applied. Case in point, if you look at the first two screenshots I shared on my original posting you'll see on the first screenshot (no filter) that March, April and August are red and everything else are kept at the default color as they're supposed to be. However when the filter is applied (second screenshot) March, April and August are still red when March should've flipped to the default color, and January, May, June, July and October should've flipped to red but they were kept at the default color.

This leads me to believe that although a filter is applied the logic somehow still looks at the base measure as if no filter had been applied.

[Jai-Rathinavel]

@leolapa_br Are you showing current year months or months from all the available years ? Can you try filtering for one year and check the formatting?

[leolapa_br]

@Jai-Rathinavel the x-axis is the month column from the date/calendar table, so it's from all the available years.

Unfortunately the database only covers 2023 and 2024 sales data, so when I set the slicer to 2023 there will be no orange line on the chart since there is no 2022 data to calculate and compare against, therefore there's no way for me to check whether this formatting issue persists in prior years.