# Looking for Answers?

## Browse our articles to find the answers you need

# Color a column chart based on another formula

If you need to color columns in a column chart based on a different formula you will need to use the following solution.

For this example, we will color the columns that calculate [Total Cost] for the [City] by another value, that is calculated by the formula count([# Visits])

1. You need to create 5 additional formulas using the function QUARTILE

2. In the formula specify the following:

QUARTILE([Dimension], [Formula], 0)

[Dimension] - [City]

For the formula, it will be - [# of unique # Visits]

3. In the first formula, you need to define 0 quartile - it will return the minimum value

4. Copy the formula 4 times to get the values for all the quartiles. In each formula change the number of the quartile from 0 until 4. You will have the following formulas:

QUARTILE([Dimension], [Formula], 0)

QUARTILE([Dimension], [Formula], 1)

QUARTILE([Dimension], [Formula], 2)

QUARTILE([Dimension], [Formula], 3)

QUARTILE([Dimension], [Formula], 4)

Make sure you save all the formulas:

2. In the formula specify the following:

QUARTILE([Dimension], [Formula], 0)

[Dimension] - [City]

For the formula, it will be - [# of unique # Visits]

3. In the first formula, you need to define 0 quartile - it will return the minimum value

4. Copy the formula 4 times to get the values for all the quartiles. In each formula change the number of the quartile from 0 until 4. You will have the following formulas:

QUARTILE([Dimension], [Formula], 0)

QUARTILE([Dimension], [Formula], 1)

QUARTILE([Dimension], [Formula], 2)

QUARTILE([Dimension], [Formula], 3)

QUARTILE([Dimension], [Formula], 4)

Make sure you save all the formulas:

5. Go to the conditional formatting of the main formula in our case sum([Cost]) and modify the conditional formatting:

6. You will need to specify 5 conditions.

6. You will need to specify 5 conditions.

Specify the following formula for the

**first****condition:**CASE

WHEN [# of unique # Visits] = [Quart0] THEN [Total Cost]

ELSE 0

END

WHEN [# of unique # Visits] = [Quart0] THEN [Total Cost]

ELSE 0

END

**Second condition:**

CASE

WHEN [# of unique # Visits] <= [Qurt1] THEN [Total Cost]

ELSE 0

END

WHEN [# of unique # Visits] <= [Qurt1] THEN [Total Cost]

ELSE 0

END

**Third condition:**

CASE

WHEN [# of unique # Visits] <= [Quart2] THEN [Total Cost]

ELSE 0

END

WHEN [# of unique # Visits] <= [Quart2] THEN [Total Cost]

ELSE 0

END

**Fourth condition:**

CASE

WHEN [# of unique # Visits]<=[Quart3] THEN [Total Cost]

ELSE 0

END

WHEN [# of unique # Visits]<=[Quart3] THEN [Total Cost]

ELSE 0

END

**Fifth condition:**

CASE

WHEN [# of unique # Visits] <= [Quart4] THEN [Total Cost]

ELSE 0

END

WHEN [# of unique # Visits] <= [Quart4] THEN [Total Cost]

ELSE 0

END

7. The

8. Specify the colors accordingly:

**[Quart0-4] (**is the saved formula for the 0-4 quartile that we saved at**step 4)**8. Specify the colors accordingly:

9. Make sure that all the conditions are set to equal:

Then you can disable the formulas to leave only the relevant columns on the widget:

As a result, you have columns that are colored according to the calculation in another formula:

Was this article helpful?