Allow me to create Pivot Table Items based on values of Columns
I have a charge number for a project that has the same prefix for all elements in CLIN 000x (0001, 0002, 0003, 0004) and want to do some Grouping on those.
No problem, I can create groups easily in the Pivot Table. I could create a column in my data. Modifying the data, as we all know, is tiresome.
Initially, I used the Excel Group feature to Consolidate everything into ChargeNumber2 as a value I could use in my ROWS or COLUMNS. However, I want to do some MATH against that group which doesn't happen because the grouping doesn't actually do anything at the row level, but only kind of pens stuff together at the summary levels. For instance, CLIN 0001 has a 10% Fee, But CLIN 0002 has a 5% fee... Let's get back to this later.
So I figured I'd try to create an Item in the Pivot table that answers the need for what value to actually POPULATE into that item field. In this case, I'd want 0001, 0002, 0003 0004 which would be achieved by a formula like: (CustomCLIN)
But creating a Calculated Item relies on only picking one something or the other in the Pivot Table. Honestly, It's kind of a piece of **** in utility and could do so much more. So I can't self populate a new column in the pivot table (that I can tell) that I can then use IN a calculated Field.
At the end, I'd create a Long Formula along the lines of to following for the Custom Calculated Field:
=(('CalculatedCLIN'="0001"*1)*('SubtotalCost'*.1)) + (('CalculatedCLIN'="0002"*1)*('SubtotalCost'*.05))
As that formula is binary math no IF statement would be necessary and I can string it out with as many CLINs as I might have.