Forum Discussion
Advocate IVSummarise Value disabling conditional formatting on another column
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
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
6 Replies
- lbendlin
Super User
I've tried to replicate the issue with some test data, but that seems to be working fine, when the value is summarisedSo you are saying you cannot reproduce the issue?
jimbob2285Hi 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
- 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
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
- lbendlin
In such situations you can streamline the code to
sub_Amount = COALESCE([AMOUNT],0) - 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
