cancel
Showing results for
Did you mean:
Creator

## Gradient color on pivot table based on each row

Hi there,

I'd like to format a gradient background color on a pivot table considering each row separately. For exemple, my goal is to show higher values in red, middle values in yellow and lower values in green, but those values are to be considered for each row separately (taking into account that a row here is assembled by the dimension "Assingment Group" and the Dates).

The final result for each row, using excel, would be this:

This is the expression on the pivot table:

Attached is a sample.

1 Solution

Accepted Solutions
Creator
Author

I think I found the solution, now I'm just playing to find the correct collors.

This was the expression to find the max per row:

Aggr(

)

Based on it, I just applied your concept of the Fractile, but using direct percentages. Now I just need to find the correct formula for the collors:

11 Replies
Specialist III

You can try something like this in the Background Color

Creator
Author

I actually did something like this, but then the max a min wouldn't be based on the maximum value of the row, it would be based on a fixed value...
I'd have to do that, but based on the max and min values of the row...

For example, if the number of tickets increases, 25 should be green and will appear in red

Specialist III

Instead of hard coding the values like I did, you can use the Fractile function to calculate the value based on a percentage.

The Fractile will calculate the value that is 50% of the max.  This way the colors vary based on the total amount.  Say red above 50%, yellow above 25% else green.

The yellow does not vary much in shade.

Partner - Specialist

Hi,

Try this expression in Background Color:

ColorMix2(

If(

<

,

(

-

)

/

(

-

) -1,

(

-

)

/

(

-

)

),

Red(), Green(), Yellow()

)

Saludos.

Creator
Author

I didn't managed to make it work. I'm stuck with the total by Group and Date. The fractile is returning the total of all items or the total for the current date only.

Creator
Author

I'm still checking your code to see what could be, but it didn't worked here...

Creator
Author

I think I found the solution, now I'm just playing to find the correct collors.

This was the expression to find the max per row:

Aggr(

)

Based on it, I just applied your concept of the Fractile, but using direct percentages. Now I just need to find the correct formula for the collors:

Creator II

Hi Marco,

Could you help me? I have the same example to color each row.

My expression to calculate values is

(SUM(RISCO_C_11_14)+SUM(RISCO_D_15_30))

/

before(sum({1}RISCO_A_EM_DIA))

How can I create a gradient color following the same your conditions to higher,medium and lower values?

Tks a lot!!!

Creator
Author

Hello Antonio, I believe you can use the solution above and just change my expression for yours.

If you're not able to do it, would be nice to have a sample of your application to look.

Community Browser