Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Liron,
It worked fine, Thanks
Hello,
have a look at the count(total distinct Location) and sum (total [Sales Margin]) formulas, they do what you need!
Hope this helps.
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
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
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!
Hi SALTO,
it did work fine,Thanks