Summarise Value disabling conditional formatting on another column

[jimbob2285]

Hi

Sorry, the link wasn't to a public location, this one is: Conditional Formatting Summary Test 

Please help, I have a simple table with conditional formatting applied to one column, that's being disabled when I summarise another column

• I've added a calculated column to count the Project ID, in a list of Activities that hang off the projects
• And applying conditional formatting to highlight where there are multiple activities for the same project 'Project Count > 1'
• But when I sum the value column to get a grand total, it seems to disable the conditional formatting, but not completely

In the Conditional Formatting Summary Test PBIX file I've tried to replicate the issue with some test data, but that seems to be working fine, when the value is summarised

So I'm at a bit of a loss, the main table and data set are somewhat more complex than in this file, but I've also stripped those rigth back, hoping to find the cause, but the problem still persists, even with the minimal data set.  But i can't replicate the issue with the test data

Can anyone explain what's goign on here and why summarising one column is affecting the conditional formatting of another column

Cheers

Jim

[v-mengmli-msft · Accepted solution]

Hi @jimbob2285 ,

This is because the Amount column contains blank value, I recommend you add a new column and check if Amount value is blank, if it is blank, then replace blank with 0.

sub_Amount = IF(ISBLANK(Submissions[AMOUNT]),0,Submissions[AMOUNT])

Best regards,

Mengmeng Li

[jimbob2285]

Hi Mengmeng Li

That worked perfectly.  In fact, just adding the new amount calculated column (with no blanks) to the visual solved the problem, I could sum just the old value column in the visual without effecting the conditional formatting - I obviously didn;t leave both value columns in the visual

Although, I'm still unsure why the blanks in the original value column would affect the conditional formatting of another column... are you able to explain why this was happening?

The test data i put together also contained a blank, but that didn't affect the conditional formatting, is it about the propotion of blanks... in my main data set the value column was mostly blank, like 99% blank

Thanks for for the solution and your help

Cheers

Jimbob

[lbendlin]

In such situations you can streamline the code to

sub_Amount = COALESCE([AMOUNT],0)

[jimbob2285]

Hi Everyone

I'm just trying to reinvigorate this one, as I'd really like to understand what's happening here

As lbendlin said... I cannot replicate the issue with a test sample, but I can demonstrate it with the original data in the linked PBIX file: Conditional Formatting Summary Test

Are there any superusers that can show my why the summury of the Amount column is effecting the conditional formatting on the project columns

Thanks

Jimbob

[lbendlin]

 I've tried to replicate the issue with some test data, but that seems to be working fine, when the value is summarised

So you are saying you cannot reproduce the issue?

[jimbob2285]

Hi lbendlin

Yes, that's correct, but if you download the PBIX file from the link in my original post, you can see that the top two tables are identical, all bar the value column in the right-hand table is summarised (Summed), which is disabling almost all of the conditional formatting on the Project Count and Project columns.

If you change the Value column in the right-hand table to Don't summarise, then the conditional formatting re-appears, as it is in the left-hand table, which already has the Value column set to Don't summarise

In the bottom two tables, I've tried to replicate the issue with soem simple test data, but cannot, and as I'm sure you'll be familiar with, it's nigh on impossible to fix a problem that you can't replicate...

So I've reached out to the gruop with an example of the strange activity in the linked PBIX

I woudl be really greatful if anyone can tell me why it's behaving in this way, there must be something that I'm missing, something that's in my original data, that's not in my test data sample, but I can't see what it is

Cheers

Jim