Hi

Posted on a discussion a while back now about colour coding within a Pivot Table. This was something that has got me thinking (scratching my head) for a while now. Visokio have a number of ideas and suggestions that come flooding in and this is small in comparison to others they receive - so was never implemented.

So I decided to carry on and crack it with their help!

Basically, I had a very large data set that I used a pivot table to report to my business some key measures (Volume, sales, share of category etc). But when using the pivot table, especially when calculating / formula in a pivot table, colour coding increases/decreases changes was proving impossible. Also too many formulas in a pivot table can slow down the report. So Visokio's advice was to calculate any formula columns within the data manager and then pull these through to the pivot table as a value rather than a formula.

In doing this, I was able to have a value calculated in the data manager, pulled through in to the pivot table as a value/measure. Then had the ability to use this value to colour the pivot table based on this. To colour code, you will need to go the "filters", "Field Options", "value ordering" and complete your colour criteria you require. Next you need to select the "Colour" option on the pivot table, "By Single Field" and select your value you have created, then "According to value field".

I have attached an example of my pivot table coloured. "TY", "LY" are values in the data set, "Var" was calculated in the Data manager View (as explained above) and "Var %" is a calculation within the Pivot table. The Pivot Table has been colour coded by the value "Var". This has made the pivot table a dynamic colour coded table depending on what you filter on.

Job done

Thanks

Danny