Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Conditional Formatting based on Previous Columns

Hi All

 

Conditional formatting is easy enough to understand, but I am struggling to apply it in the way I want, so need some help.

 

Here is my basic table.  It shows data use over a couple of months, and I want to simply highlight if that particular user has increased or decreased their use each month.

 

The data comes from 3 seperate data sources whcih will be added to up to a max 6 month period.

 

But how do i ask conditional formatting to look at the previous column, then format accordingly?!

 


Any help most welcome.....thanks

Need Help
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

     

    You should be able to use something similar to this:

     

    I created a new Calcultated Column called 'Nov_Higher_Lower' in this column I return a value of -1, 0 or 1 depending on the sign of the number when I subtract Novembers value from Octobers value. 

     

    Nov_Higher_Lower = SWITCH(SIGN('Table1 (2)'[Nov 2019(kb)]-'Table1 (2)'[Oct 2019 (kb)]), 0, 0, -1, -1, 1, 1)
     
    I then run a Rules based conditional formatting on 'Nov 2019(kb)' based on the results of 'Nov_Higher_Lower'
     
    You would also do the same with another new colum for Dec_Higher_Lower .....
     
    I'm sure there might be a cleaner way to do this, but I hope this helps.
     
    Sduffy
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for the suggestion Anonymous  - appreciate the help, but seems there is an issue reading the October data - which is on a seperate data source?

       

      Used the following:

      Nov_Higher\Lower = SWITCH(SIGN('Nov2019'[Volume (kb)]-'Oct2019'[Volume (kb)]),0,0,-1,-1,1,1)

       

      But the result is:

      A single value for column 'Volume (kb)' in table 'Oct2019' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.