I presume you have a crosstab, generally looking at one measure (revenue), teams vertically and team members horizontally.

You could then do it by adding three calculations:

1. Add "as a new measure": if sum(0, 0, m1) > 500 then 1 else 02. Add as a new column: count(all, 0, m1) ... this would be total number of team members.3. Add as a new column: sum(all, 0, m2) ... this would be team members above the threshold.

Finally, go to the Properties tab, Visibility, to hide your two measures (the original one and your calculated one). That should end up with your desired two numbers for each team.

BR / Ole

I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

yes that works to a point, in the actual version there is one complication which is causing me an issue and i hadn't comprehended the issue when i posted originally.

Yes it is a crosstab,

Vertically i have teams, horizontally i have team members.

I have one measure turnover, but the complication is as follows.

I have a comparison on the horizontal, that is designed to return as the first number turnover of one group of products, and the second element is turnover on a different group of products.

What i have then created is two new measures, both of these measures are based on if value>threshold then 1 else 0 this then tells me if a team member has exceeded the target for group a and also the target for group b.

The point of this is to identify team members who have sold the first group and who have not sold products in the second complimentary group. the indicator being percentage of team selling products to the expected level in both categories

i have then added two count columns etc

The issue i have is that the sum(all) includes both group a and group b from the comparison.

What i need to achieve is all team members who have sold group a above the threshold and all team members who have sold group b above that threshold.

So what i need is sum(all(comparison group a)) and sum(all(comparison group b))

I have broken the image into its parts, and colourcoded which measure depends on which.

I have put a filter on the result to only have one sales team, the first group is group a products, and note weight says 0 this is because the weight is too low to count,

note in the second group the margin says 1 because it is high enough.

What i need in the third group is weight to be 0 and margin to be 1. if i were to expand the whole the third group 1 should be a sum of all first group products and margin should be a sum of all second group margins.

It is almost there, i now have the correct number of agents who have crossed the group a threshold. and i also have the number who have crossed the group b threshold.

However there is one hopefully minor issue remaining, the correct answer to group b is those who have crossed the group a threshold and the group b threshold.

ie i have in one sales team 14 who have crossed the group a threshold, and 15 who have crossed the group b threshold, but the answer is who of the 14 have crossed the group b threshold, which should be 9.

I need this basic code tweaked to included a reference to the other group,

if allcount(d1:0(l(1,0)), d1, m1) = 1 and sum(0, 0, m1) > 500 and (REFERENCE TO OTHER SIDE OF COMPARISON) then 1 else 0

What i need to be able to do is query the value of a measure in the other half of the comparison to see if it has exceeded a threshold?

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

We use cookies to improve your site experience, but they also provide us with information on your use of our website.
To find out more about the cookies we use and how to delete them, see our Privacy Policy. By continuing to browse the site, you are consenting to our use of cookies.