How to color code a column in visual cues

[#1]

Hi ,

I have attached my QVW file, what i am trying to achieve is , I have a column named margin and i am trying to color code the margin based on a formula

i.e.  in the visual cues for the upper count i gave it as

=SUM([Sales Margin]) > (SUM([Sales Margin]) / Count(Location))

and for lower count it is

=SUM([Sales Margin]) < (SUM([Sales Margin]) / Count(Location))

basically what i am trying to do is , if the total margin across all locations / no of locations which gives me the avg margin ad if that location margin is greater than avg margin then upper count else lower count.

But it always shows me only upper count?

Can anyone suggest me where i am going wrong?

Thanks

[lironbaram · Accepted solution]

hi

you were close

just change your expression to

=(SUM([Sales Margin]) / Count(Location))

you don't need the first part as it already calculated  in the expression

so you just need to enter the value you want the expression to be compared with

[#3]

Hi Liron,

It worked fine, Thanks

[salto]

Hello,

have a look at the   count(total distinct Location) and  sum (total [Sales Margin]) formulas, they do what you need!

Hope this helps.

[#5]

Hi Liron,

It works fine however, i need to make a small change to this one, The margin color code shouldnt change if i select any location it should be the same .I know it can be achieved by set analysis, but i am not exactly sure on how do it.Can you help me with any ideas?

Thanks

[#6]

Hi SALTO,

It works fine however, i need to make a small change to this one, The margin color code shouldnt change if i select any location it should be the same .I know it can be achieved by set analysis, but i am not exactly sure on how do it.Can you help me with any ideas?

Thanks

[salto]

This may help you:

=(SUM({1<Location=>} total [Sales Margin]) / Count({1<Location = > } total distinct Location))

Change the conditions in the visual cues and you are done.

Regards!

[#8]

Hi SALTO,

it did work fine,Thanks