Conditional Format Across Columns

User_2NI9B

Hi Everyone.

So, I need to compare a month with its previous month and do the format accordingly. Green means it grew, red means it dropped. How can we achieve this?

Thanks

                     

Region	201701	201702	201703
West	100	110	90
East	200	290	400

Robert Angel

Create a formula based column as; month - previous month (full syntax from the formula browser, I usually copy one column and then just minus the other by copying its formula in)

Create a conditional format based on this column which changes the format on the other column based on the value <=0 >0 (or whatever)

Hide the formula based column

Job done!

User_2NI9B

Would you please elaborate a bit? Not sure if I understood. My report has filter for three months that are fed by dashboard prompt.

Robert Angel

Okay, breaking it down, do you have a Previous Month Measure in your rpd, if so you are using that to calculate month - previous month, if not then you first need to create the equivalent (look into AGO functionality) and then apply that to three columns.

My logic in the first post is basically explaining how to do it for one column.

Personally I like time series graphs for this, upward and downward tell a better story, but then I am colour blind so maybe I am biased....

Thomas Dodds

You should have a basic set of logical metrics for each measure you have ... use of information drives data design.

When a client says they need 'Sales' ... my response is "by what over what?"

From there if I have a simple "by Employee over Month (monthly)" then I automatically build:

Sales
Sales Year-to-Date
Sales Month over Month (with variance and percentage change)
Sales same Month Last Year (with variance and percentage change)

Sales Contribution

etc...

These are logical so should be built in the BI layer (RPD) and not on the front end ever.  If you need performance, then push these back to the physical database (and take the hit in ELT processing over-night).  NOTE:  if you do this on the front end - how will you color January given that you've got no December shown?  These complementary metrics would also be on the same time grain as your base measure as they are logical.

Once you have all these ... it's easy to display them or use them to drive a lot of informative and actionable insights.

Also, @Robert Angel has pointed out a fundamental in data presentation -- choose wisely as you want the user (any user) to get the same actionable conclusion at first glance.  Having been involved in many many BI endeavors - I know the value of what he's counseled.  If you insist on red/green add a black indicator as well (up/down arrows; +/-); you'll reach a larger audience and potentially one that you don't even have looking at your stuff at present. 

Teresa Modesta

Hi sweetie,

word from one who has been there recently - do mark their help as helpful / answered and / or close your question if you ever want to ask another question again on these boards...

If they have not answered your question or you do not understand their answer then do give them more detail, they can only do so much working in the dark...

First time I didn't I nearly got a smacked bottom...