Is there a way to incrementally count unique values per grouping?

For example,  I have the table below with Group and Category columns.  For each Group, I'd like to know assign a incrementing counter for every unique Category value.  Rows the the same Group and Category combination should have the same Counter value (ex. Group 1, Categ A rows have the same Counter value).   Thanks in advance.

I really hate saying that something cannot be done in Apptio, but I am going to tell you that you've broached one of the hardest things to do within the platform.  Apptio is not a transactional system and does not apply formulas in such a way that you can increment any value based on other rows of data.

With that being said, here is how I would approach your query.

I would start off with just the data - Group and Category - in a table.  From here there will be several transforms.

My first assumption is that there is a known, and reasonable (to be evident in a few moments) number of Group values.  I would then create a transform of my table for each Group.

I'll now focus on Group 1 for my example but this same logic applies for all Groups.

Next I would group the table down on 'Group' and 'Categ' giving me my list of unique values, followed by a Formula step where I add the following two fields:

Counter         =Row()+1

Lookup Key   =Group&&Categ

The Row() function just lists the row index for all lines in a table, but it is fluid based on the state of the system and so is not really effective as an identifier.  But for our purposes, it is the only way we can count within a table.  We add 1 because the Row() function is a zero-index counter (i.e. starts counting at 0).

This now gives me a table that looks like this:

We could bring this together in two ways, depending on how you needed the data:

1. We could create a blank table and append the individual groups tables together, bringing in all unique 'Group' and 'Categ' combinations
2. We could perform a series of Lookups and collapsing logic to return the 'Counter' values back into our original table, preserving the original data.**

**As an important note, if you were to do this via lookups it would be necessary to create one more transform off of the original table as your "Final" table.  This extra transform is required to prevent circular references.

So I'll end with, it's not impossible in Apptio.  But there would be a bit of leg work to get you to where you want to be.

Thanks for the corroboration Gregory!

@Karen Lim, @Gregory Nutt - I had the same type of use case to solve for and added a Flatten Hierarchy step to the pipeline to fix it.  Not what it's supposed to be used for apparently, but works (for my use case anyway). Flatten dataset with duplicate children