Forum Discussion
Conditional formatting logic keeps reverting back to percent/number
- 1 year ago
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.
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.
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_br1 year ago
Resolver II
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_Geraldo1 year ago
Super User
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_br1 year ago
Resolver II
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:
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!