Colour Scale groups of values according to their category

[Centre13]

Hi - I have a column of figures and I want to colour scale them. However I need a formula that colour scales the groups seperately. i.e. the traders are colour scaled within the trader group and the salesman are coloured scaled in their group.

Any help would be much appreciated.

Example: $
Trader 100
Trader 150
Trader 200
Trader 250
Salesman 100
Salesman 75
Salesman 60
Salesman 50​

 

[GDRIII]

Assuming this is data in a Power Pivot model, since this is the Power Pivot forum, you can achieve this by converting your measures to CUBE Formulas.

Once you have your CUBEs figured out you can reference them in your conditional formatting rules.

This article uses a bit of both techniques to do the same

Toggling Conditional Formatting On/Off via Slicer « PowerPivotPro

 

[Centre13]

not sure I follow. Do I need to convert category into a cube value i.e. saless trader?

 

[GDRIII]

Lets suppose your measures are called [Sales] and [Trader]

If you convert those measures into CUBEVALUES, you can have them mirror the data in your Pivot and have them be controlled with the slicers as well.

You can "hide" those CUBEVALUES somewhere in your sheet out of view and then reference the cell in the Conditional formatting box.

This way if you want Red anything over whatever the values are based on slicer selection, your conditional formatting will work.

Two pivots, both controlled by the same slicers, one converted to CUBE VALUES and hidden, reference those cells in your conditional formatting on the "main" pivot

To directly answer your question, convert (or write, the synatax is sort of easier once you get the hang of it) any and all measures that you want to base your Conditional Formatting on.

Clearer?

Coffee still kicking in...

 

[Centre13]

wow never new these Cube formulas existed. manyt thanks. Believe this is a better way to create the dashboard I need as opposed to a Pivot table. I converted my pivot table to formulas. How do you add a column to be referred to if you forgot to put it in the original pivot table?

 

[GDRIII]

You need to fully shape your table before converting it. Once you convert, it's stuck in CUBELAND.

You can do some very powerful things using all of it in combination...

Some articles here, I'm sure would interest you.

searchresults « PowerPivotPro

 

[Centre13]

Many thx for your help. Think this is the beginning of a new adventure..