I want to consolidated related rows and instead of {Various}, I want to see the various data points separated by a semicolon.

See example below:

Original Data:

Transformed into:

Has anyone done something like this before in Apptio? I know how to do it in Excel and can use VBA to do it in Access (http://allenbrowne.com/func-concat.html)

We do not have a function that will directly/easily do this right now.  However, there are a couple of ways you could achieve this - the easiest way I can see is:

Assuming the name of your table is 'OriginalTable', create a transform of it (to get around a formula circularity guardrail that otherwise occurs).

In the new transform, make a formula column with a formula of:

=UniqueValues(OriginalTable:Resource Towers[Cost Center=Cost Center])

This will produce a column with cells like: "Apps","Compute"  (which is pretty close to what you're looking for).

Regards,

Paul

Building on this, you can also use the "substitute()" function to replace the double quotations with a blank (empty string ""), and the commas with a semicolon, which would change the result to:

Apps; Compute

Which is the desired final format.

Yeah-- I have gotten to that point before. The reason I am trying to do this is because I don't want dollars allocated to this object (data set) to split dollars from CC102 between Apps and Compute line items.

For reporting, I actually want Apps; Compute to show up as one line item. Because it would be misleading to say that Compute costs from CC102 are X amount of dollars and vice versa for Apps. But saying that X amount of dollars are Apps; Compute is a more accurate statement.

Did that make any sense? haha

Hey Gboyega,

Just to clarify your question a little further, how are you allocating costs from the sending object to the receiving object, and what is the relationship between the two objects?

I'm curious because typically if you're allocating "X" dollars from CC102 to Apps/Compute it shouldn't allocate the full "X" to both Apps and Compute, but rather a proportion of "X" to Apps, and a proportion to Compute, based on the selected weighting.

Hey Michael,

This is just a hypothetical example, but this should give a clearer picture of why I want to do this.

The from object is a list of cost centers associated with their related resource towers and project cost.

(CC | Resource Tower | Project | Cost)

• CC101 has $1M associated with Project X, and all people in CC101 are Apps staff
• CC102 has $2M associated with Project X, and some people in CC102 work on Apps and others are Compute
  • we cannot determine the percent of the $2M that is related to Apps versus Compute
• CC103 has $500K associated with Project X, and all are IT Management

The to object is a list of projects. So when reporting on Project X, I want to see what dollars from each resource tower, not cost center (CC).

For CC102, I want the all $2M to be labeled as "Apps; Compute", rather than seeing "Various". Reason this is critical is that if the costs are split evenly to Apps and Compute, that means that $1M is Apps and $1M is Compute, and that would be misleading.

End state should show:

• Apps - $1M
• Apps; Compute - $2M
• IT Management - $500K

Instead of showing:

• Apps - $2M
• Compute - $1M
• IT Management - $500K

Okay, makes a bit more sense now!

In order to do that we'll have to take a couple of steps:

1) In the transform of the first object, add a new field which combines Cost Center and Project

2) Create a transform of this object (required in order to use the =UniqueValue() function, to avoid the self-reference check). Create a new column to pull the unique resource towers by Cost Center and Project:

=UniqueValues(Forum Example Raw:Resource Tower[CC and Project = CC and Project])

3) In the 2nd file, increase the granularity of the data set by using a lookupex() to pull over the Cost Center and Project breakout data:

4) Next, add another column with a regular lookup() to pull over the unique IT Resource Towers

=lookup(Project Breakout, Forum Example Transform, CC and Project, Project Resource Towers)

5) Make sure you've set the granularity of the 2nd object to the "Project Breakout" field (if you're using a standard object then you can simply map the "Project Breakout" field as the "Unique ID"), then set the Cost Center and Project breakout field as a key in both data sets, and link the two objects together.

6) In the reporting surface you should now be able to see reporting at the level desired!

Let me know if that works as you were thinking!

Yes-- this is exactly what I am trying to do. But the problem that I am having is your starting point.

I want Apptio to do the work of consolidating the resource towers field. I don't want to have to upload this mapping file every month because I already have a data set in Apptio that associates the cost centers with one or many resource towers. I just want to create a transform (or generated table) that groups the cost centers, but retains the resource towers by listing them out rather than putting {Various}.

Sorry if my explanations earlier were off...

Is there a way to create a transform of a table that transposes the data?

My thought process is that you have a transposed table with a column for each cost center. The row values contain the resource tower. You can then create your generated table using the UniqueValues function, which would concatenate all of the Resource Towers by cost center.

Thanks everyone for the insight and ideas. Originally I misunderstood @Paul McLachlan suggestion, but it's right on the money. I have recreated the example in Apptio to show how I got it to work.

The original data set:

Transform:

Cost Center: Group By checked

Resource Tower (Value Override): =UniqueValues(Concatenate Example:Resource Tower[Cost Center=Cost Center])

Quotation Mark: =left(Resource Tower, 1)

This is necessary because you can't substitute a quotation mark because the formula gets a little confused. So since the first character of the unique values formula is always a quotation mark, we can use the left function to be the reference within our substitute function.

Remove Quotation: =Substitute(Resource Tower, Quotation Mark, "")

Final Output: =Substitute(Remove Quotation, ",", " & ")

Thanks for this article - exactly what the boss asked for!!