Heat map for percentile values

[rdsuperlike]

Hi

I am trying to generate a heat map for date Vs steps(for eg 1-10)

I have sum(sales). But to generate color in my heatmap. I need the highest sales over all dates and all steps (these 2 are my dimensions

Dimenion: date

dimension: steps

Below expression gives me the percent of sales for that specific cell with respect to max value in that pivot table.

Expression:- Sum(Sales)/max(aggr(SUM(Sales),Date,Steps))

Then i used colormix wizard and gave upper limit as 1, intermediate as 0.5 and low limit as 0.0 to give me appropriate colors

But i want to show colors in form of percentile instead of percentage. What change should i make to my expression?

Thanks in advance. I tied using fractile function but getting errors.

[Gysbert_Wassenaar]

But i want to show colors in form of percentile instead of percentage

What does that mean?

[rdsuperlike]

I used (v2-(Rank(Total sum(sales)/v1)))/v2 which gives me percentile value and when i put it in colormix wizard it gave me colors from red to yellow to green for upper limit-> intermediate limit-> low limit for 1->0.5->0 values respectively.

Here v1 =max(aggr(SUM(Sales),Date,Steps))

v2 = max(aggr(Rank(Total sum(Sales)/v1),Date,Steps))

I am able to get proper values and colors for straight table when i use the expression and the two dimensions.

But as soon as I try to create a crosstab/pivottable it all messes up. The rank function doesnt seem to be working here.

Can you suggest a substitute for rank function here? Or some other approach