How to Rename a Unique Identifier Sum

Question

Hi, I am trying to figure out if it is possible to use a formula to achieve my desired outcome. I have specific cost centers and job levels (of employees), I want to add those unique counts together so example if I have cost center 1234500000 and job level 4, and I have a count of 5 new employees for this unique identifier. Is there a formula that can bring back: TBH 1, TBH 2, TBH 3, TBH 4, TBH 5? And if there was another instance of cost center 6789000000 and job level 2, with 3 new employees, for the column to bring back: TBH 1, TBH 2, TBH 3. The "TBH 1-5" would simply be the new column I would like to create based off the criteria above. Please let me know if this is possible to do within a formula or in Cost Transparency.

#ApptioforAll

Accepted Answer

I agree with @Mark Nealey that it's hard to come up with ideas, but I'll give it a shot.

First assumption

Taking your text literally, @Alejandra Roman, I build the following:

If so, the following formula will calculate the sum of all five TBH* columns:

TBH1-5 =TBH1+{TBH2}+{TBH3}+{TBH4}+{TBH5}

But it will not calculate the sum of the costs associated with the employee salaries.

Second assumption

To get the costs based on fixed rates, you may want to expand the table as follows:

Those need not be hard-coded, you may get them from ancillary tables via Lookup(), TableMatch() or something like that.

Then we calculate the costs for each of the TBH columns:

And, at last, the Total Cost for TBH1-5:

Final comment

Cost Transparency will automatically aggregate all the line items related to a Cost Center if you have the data in the appropriate way. E.g.:

You could still keep the detail by job category if required:

Should any of these options not suit your purpose, just let me know the details and I'd write a more comprehensive blog post.

#ApptioforAll

Apptio for All