Color Mix Not updating background colors

[#1]

Hello,

Can someone tell me what is wrong with this code?  I'm trying to create background colors (mimic Excel) over a range of values (Ratio).  I would like to set it up so

Upper is Max(Ratio)

Lower is Min(Ratio)

Intermediate is in between Upper and Lower

My code isn't working and I don't understand why.

I have attached an example.

Appreciate some help, please!

[jonathandienst]

Hi

You need to use Aggr() to nest the Avg inside a Min or Max:

=Max(Aggr(Avg({<DateComp={'Post'},[Exchanged]={'N'}>}[Amount]) / Avg({<DateComp={'Prior'},[Exchanged]={'N'}>}[Amount]), Other))

=Min(Aggr(Avg({<DateComp={'Post'},[Exchanged]={'N'}>}[Amount]) / Avg({<DateComp={'Prior'},[Exchanged]={'N'}>}[Amount]), Other))

HTH

Jonathan

[#3]

Hello

Thanks for the help.  I was able to get it working by using

RangeMin (top(total Avg({<DateComp={'Post'},[Exchanged]={'N'}>}[Amount]) / Avg({<DateComp={'Prior'},[Exchanged]={'N'}>}[Amount]) ,1,NoOfRows(total)))

RangeMax (top(total Avg({<DateComp={'Post'},[Exchanged]={'N'}>}[Amount]) / Avg({<DateComp={'Prior'},[Exchanged]={'N'}>}[Amount]) ,1,NoOfRows(total)))

RangeAvg (top(total Avg({<DateComp={'Post'},[Exchanged]={'N'}>}[Amount]) / Avg({<DateComp={'Prior'},[Exchanged]={'N'}>}[Amount]) ,1,NoOfRows(total)))

I am going to try your suggestion tomorrow!

[deepakqlikview_123]

Thanks Manish