Conditional formatting stops working when using slicer

[y_s_c]

Hi there,

I've got a matrix with some basic measures that display calculated percentages for, say, a bunch of companies in different geographies. The matrix uses condititional formatting to colour the cells' backgrounds based on another measure used for the field value formatting.

The measure I'm using for the field value formatting style looks something like:

EU companies colour formatting =
IF (
    [EU companies %] = BLANK (),
    "",
    IF (
        [EU companies %] < [Total %],
        "#feb5b1",
        IF ( [EU companies %] > [Total %], "#cee2cd" )
    )
)

Then if I add a slicer to the page to filter by companies (e.g., EU companies, US companies, African companies) the colour formatting disappears for the selected company-type, i.e., if I select EU companies the colour formatting stays for the rest of companies but disappears for the column showing the calculated percentages for EU companies.

Could someone please explain this behaviour to me? Any and all help very much appreciated!

EDIT: the colour formatting remains if the slicer filters for something else other than geography (e.g., size of company). Filtering using any variable used in the creation of the measure [EU companies %] results in the formatting disappearing.

[Joe_Barry]

Hi @y_s_c 

Can you share how you created both measures? Are both measures calculated from the same table? If not, do both tables share the same dimensions?

As @v-yaningy-msft  mentioned, if you could share a PBIX or provide more information on your data structure. Relationships, Measures etc, this will help us find you a solution

Joe

[y_s_c]

Yeah, my bad. I replied to v-yaning-msft below with an example file. Thanks!

[v-yaningy-msft]

Hi, @y_s_c 

Didn't simulate your problem, you can share pbix files without sensitive data for testing.

Best Regards,
Yang
Community Support Team

[y_s_c]

Sorry, thought I was missing something obvious that wouldn't require an example file. My bad.

Here I recreated the issue with a very simple and small example: https://we.tl/t-EhaDwWc50t

There's one table with data and one table with measures. If you use the slicer for "Continent", the column with the selected continent loses the colour formatting. Probably the answer to my question is rather simple and one of those "facepalm" moment, haha.

Thanks!

[Joe_Barry · Accepted solution]

Hi @y_s_c 

I think the issue is caused by the ALL(Table1[Something]) in the measures.

Can you tell me the bahaviour you would like to acheive in the visual?

Example when you filter Europe what do you want to see? Do you want the colour formatting to Jump to Europe, but still see all the other continents?

The colour coding logic doesn't make sense, you will always have less than All and Never More than all. Is there a % value that we can measure against?

Asia colour formatting = 
IF (
    [Asia %] = BLANK (),
    "",
    IF (
        [Asia %] < [All],
        "#feb5b1",
        IF ( [Asia %] > [All], "#cee2cd" )
    )
)

[y_s_c]

The colour measures draw comparison to [Total %], which is a simple measure that computes the proportion of all "Somethings" irrespective of continent. The measures make sense and colour format the cells properly if the slicer isn't used (see screenshot below). The colour simply displays red for those continents below the "total %" or green for those above. [All] isn't a measure in the example pbxi I linked in my previous post 🙄

I'm just curious as to why when the slicer is activated, the selected continent stops displaying coloured cells, e.g., if filtering by "Europe", the matrix will show no colour under "Europe %" but the rest of columns will keep showing the formatting. An example of this below:

Hope that makes sense! It isn't a huge problem because this isn't a real-life issue of mine, but came across this behaviour and wanted to understand it 😄

[v-yaningy-msft]

Hi, @y_s_c 

Thanks for @y_s_c  reply. My opinion is the same as his, the Measure you wrote doesn't reflect the judgment situation when the two are the same. After the data is filtered, [Asia%] and [Total%] are equal, so naturally there is no color shown. 

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum