Making a graph with two "by" fields

I would say if you wanted to create an additional field that held the "Goal Number" you could use that to base line.

But, how does that number get determined?

There is a very "kludgy" way to do this. If you follow the instructions I provided above to create a custom MDA object, add a new row to the data called "Goal" and then add an additional step in your setup action in the rule to push this goal number. 

You can even set different goal numbers for the CEM and CSM roles.

Then the raw number and goal appears for each team member in the report if you set the aggregation on the goal number as max type and select visualization type of column line.

🙂

Wayne beat me to it while I was busy building. 🙂

Thanks Wayne and Dan! That goal number is just determined manually each quarter. So this quarter it is 30. So I just need to make goal number be 30.

lol, yours was definitely more in depth though. 

Thinking about what that 'Goal number' should be. If there is no set number perhaps doing a customer wide average (benchmarking it basically) and using that as a base line for every customer 'goal line' is a good direction. 

Also, I'm worried that your step three may need an adjustment. ***Edit*** you have it setup right. But, instead of an insert I would upsert both utilizing the Account name, user, and a hard coded role field (to account if the same user is both owner and csm) as your identifiers. This way if the CSM changes you are not creating a whole new record.

Let me know if you need to go more in depth, I can take screen shots and post them.

Yeah, I had some bad naming convention in the first set of steps. The rule does push them into the same column using insert instead of update or upsert.

If you take a look at the last report I had updated the name of that field in the MDA object to be account owner instead of CSM:

Is this what you had in mind? Or did I miss something different?

Nope, you are right. I modified my original post. But, I would do an upsert instead of an insert incase a CSM changes.

I think you might have a problem if you do an upsert as the data structure implicitly requires duplicates as the relationship from account name to account owner is 1:M.

For example, see the example below where for the same account name we have two "owners" each with a different goal number (since they have different roles):

An upset would overwrite the older of the two entries, I believe.

Since CSM and CEM assignments might change, it would make sense to periodically flush the table and just rerun the rule that populates it so that the data is always fresh.

Correct, unless you make use of the 'role' field. I built it out for screen shots. Also, not sure if bionic rules is necessary. Does the owner and CSM live on the same object? Anyhow here is how I set it up.

1) Fetching all of the data.

2) Pushing the Data for CSM, notice the identifier for the Account ID and Role(Hard Coding CSM).

3) Pushing the data for Account Owner, both account and role are identifiers again but, this time role is 'Account Owner' not 'CSM' since it does not match it will push a new record.

4) Your output looks like this

This gives you the ability to run the rule daily and never have to wipe it. 

That is a killer solution, Wayne! Love it!!