Search code examples
excelpivotcomputed-field

Computed field on pivot table

here there is my table

N   tag
1   7-3
2   3-4
3   2-4
4   5-3
5   4-6
6   3-1
7   3-1
8   3-4
9   5-6

I need to create a Pivot Table (I do not know how many rows there will be) with the first value of Tag. Example : 

B2="7-3"  ---> =VALUE(LEFT(B2;FIND( "-";B2 ) - 1 )) = 7

So the expected table should be:

N   FirstTag
1   7
2   3
3   2
4   5
5   4
6   3
7   3
8   3
9   5

When I create the computed field for the pivot table I get error: enter image description here

Can you help me? Riccardo

Solution

  • You don't have access to all of the normal functions in a Calculated Field. Check out this previous Q&A about the topic. It looks like you may only have access to SUM. I have not confirmed that though.

    I would recommend creating a Table with the formula added as a column formula. This allows it to apply to all of the rows that you don't know exist yet. You can then summarize the Table with a Pivot Table and the Data Source will expand/contract as needed.

    Picture of resulting table and Pivot, not sure what the Pivot is for so I just put the fields in a hierarchy.

    picture

    Formula in FIRST TAG

    =VALUE(LEFT([@TAG],FIND( "-",[@TAG] ) - 1 ))