Forum Discussion
Resolver IIConditional formatting logic keeps reverting back to percent/number
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...):
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.
13 Replies
- Jai-Rathinavel
Super User
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?
- 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:
- Review your measure.
Check if functions like ALL, REMOVEFILTERS, or CALCULATE are used in a way that ignores your slicer or visual filters. - 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.
- Review your measure.
