Colour the Range based on values

[richard24best]

Hi All,

I need your expertise on how i can colour min, max and average using my expression. Attached the table snapshot and what i am looking for.

My Measure to get number value and percentage togeether - 

(count(distinct if([CTMP Local Days TIL Match Agreed T Or Greater]='Match Agreed Greater Than T+0' and [CTMP Match Status]='Matched',[CTMP Trade Side ID])) &'('&(round(count(distinct if([CTMP Local Days TIL Match Agreed T Or Greater]='Match Agreed Greater Than T+0' and [CTMP Match Status]='Matched',[CTMP Trade Side ID]))/count(TOTAL distinct {<[CTMP Local Days TIL Match Agreed T Or Greater]={'Match Agreed Greater Than T+0'},[CTMP Match Status]={'Matched'}>}[CTMP Trade Side ID]),0.0001))*100 &'%'&')')

Dimensions - 

1) =if([CTMP Local Days TIL Match Agreed T Or Greater]='Match Agreed Greater Than T+0',left([CTMP Security Code],2))

2) =IF([CTMP Joint Touch Statement]<>('No Touch Cncl/Unmatched'),[CTMP Joint Touch Statement])

3) =[CTM FI Late]

My current view of table below -

The below is the view i want to achieve -

Please check and advice.

Thanks in advance,

Richard

[Kushal_Chawda]

@richard24best  can you copy paste the actual expression(not color one) here?

[Kushal_Chawda]

@Steven_Haught  Doesn't seem to be rank issue here. Probably if we get sample app then only we will be able to know exact issue.

[richard24best]

@Kushal_Chawda  please find the expression which i am using to calculate - this gives me number (percentage)

count([CTM FI Late]) & ' '&'('& (round(count(distinct if([CTMP Local Days TIL Match Agreed T Or Greater]='Match Agreed Greater Than T+0' and [CTMP Match Status]='Matched',[CTMP Trade Side ID]))/count(TOTAL distinct {<[CTMP Local Days TIL Match Agreed T Or Greater]={'Match Agreed Greater Than T+0'},[CTMP Match Status]={'Matched'}>}[CTMP Trade Side ID]),0.0001)*100 &'%'&')')

[Kushal_Chawda]

@richard24best  how about this? If still not working please share sample app to look at

if(Count([CTM FI Late])=median(total aggr(Count([CTM FI Late]),[CTMP Joint Touch Statement],[CTMP Market Country],[CTM FI Late])),RGB(255,13,13),
if(Count([CTM FI Late])=min(total aggr(Count([CTM FI Late]),[CTMP Joint Touch Statement],[CTMP Market Country],[CTM FI Late])),green(),
if(Count([CTM FI Late])= max(total aggr(Count([CTM FI Late]),[CTMP Joint Touch Statement],[CTMP Market Country],[CTM FI Late])),RGB(255,192,0))))

Note: Make sure to include all the dimensions in aggr 

[sunny_talwar]

@Steven_Haught thanks for tagging. To begin, I would suggest @richard24best to create a new field for the calculated dimensions in the script because that is the only way we can use those within the Aggr() function. Once we have that, the expression might just work or it might not, but without having at least the 1st dimension created in the script, I don't see how this can be done.

If([CTMP Local Days TIL Match Agreed T Or Greater] = 'Match Agreed Greater Than T+0', Left([CTMP Security Code], 2)) as [New CTMP Security Code],

If([CTMP Joint Touch Statement] <> 'No Touch Cncl/Unmatched', [CTMP Joint Touch Statement]) as [New CTMP Joint Touch Statement]

and now use the two [New ....] dimensions in your chart as dimension and also in your aggr() function 

[richard24best]

Hi Sunny/ Kush/ Steven, 

Well i have too many tables with similar expectations so i might end up creating various calculated dimensions.

Instead i am happy to use Median as solution which i shared earlier as it serves the purpose to certain extent.

Thanks everyone for your time and support.

Regards,

Richard