Pivot Table field in Filter and Row referencing to the same column in the data source

[couthelle]

Hi, I took over a report. It has a pivot table that has two fields (Field and Field2 as highlighted on the screenshot). These two fields are referencing to the same column in the data source called "Field".

If I double click the 1 data values on the pivot table to generate the data source tab for that value, I won't see "Field2" column.

If I rename "Field" on the data source, the pivot will update -> "Field" on the pivot table will stay but "Field2" will disappear.

I want to know how the previous user created "Field2". Usually I can only reuse the field on the pivot table if I used it as data value. For instance, "Field" added to Filter & Data Value. But in this case, "Field' was added to Filters and added again on rows as "Field2".

I also tried creating a Calculated Field, but it doesn't work the same way.

Appreciate any of the feedback and help.

 

[Alex Blakenburg]

If you select Field and right click and select field settings, what is in the Source Name box.
Then do the same to Field2.
Is the source name really the same ?

 

[couthelle]

If you select Field and right click and select field settings, what is in the Source Name box.
Then do the same to Field2.
Is the source name really the same ?

View attachment 77798

Thanks for reading my post...

FYI, instead of calling "Field" and "Field2", I'm going to use the actual field name on excel which is Stage ("Field") and Stage2("Field2")

The source name for Stage is Stage and source name for Stage2 is Stage2

However, if you try to check where exactly is that stage2 data is coming from, you will see that there's no column header = "stage2"

This screenshot is the actual data source tab. This is to show that I highlighted Row 1 header column to search for "Stage2" column header and nothing appeared.

This second screenshot is the table generated when you double click the value. Stage column header appears but once again Stage didn't show up on search.

If I renamed the column header from "Stage" to something else, "Stage2" will disappear.

 

[Alex Blakenburg]

On a copy of your workbook try this.

• Switch your table layout to tabular.
  Design > Report Layout > Show in Tabular Form
• if stage2 is not yet in the table, then go ahead and add it to the Row section.
• Right Click on any item in the stage2 column.
• If you can see Ungroup select that.

Let me know if that does anything.

 

[couthelle]

@AlexBlakenburg thanks for the reply, but how do I create the "stage2" field?

On the Sample tab, you will see that I have "Stage" under Filters and "Stage2" under Rows

Data source is in the Data tab. As you can see on the Data tab, it doesn't have the "Stage2" column.
From how I can see it, "Stage2" is derived from "Stage" -- that's why I believe that "Stage2" is using the same data source as "Stage".

 

[Alex Blakenburg]

It might be unlikely but did you try what I suggested in post #4 ?
Right Click on Stage2 and Ungroup ?

Are you able to put the Sample file (with the pivot) on a shared server such as Drop box, Google drive etc and make it available to anyone with the link and post the link to it here ?

 

[couthelle]

Hello Alex,

Thanks for your reply.

Yes I tried to ungroup. Stage2 completely disappears and you won't also find it anymore on the PivotTable Fields.

As for the file, I'll try to see how I can publish it. I'm afraid our workplace has strict measure to not allow us to share any of work documents (e.g. used of Google Drive is blocked).

Truly appreciate your help

 

[Alex Blakenburg]

I want to know how the previous user created "Field2". Usually I can only reuse the field on the pivot table if I used it as data value. For instance, "Field" added to Filter & Data Value. But in this case, "Field' was added to Filters and added again on rows as "Field2".

Yes I tried to ungroup. Stage2 completely disappears and you won't also find it anymore on the PivotTable Fields.

The fact that it disappeared when you ungroup it, pretty much confirms that the field was generated by the grouping function.

What I can't work out is how they got it to fully clone the original field. As far as I can tell you need to Group at least 2 items, after which it will display that group and all the ungrouped ones.
Are Growing, Farming, New Plantation, Existing Plantation visible in Field or only in Field2 ? Any that are visible in Field2 but not in Field would be the grouped items.

 

[couthelle]

Hello Allex Blakenburg,

I finally got it, thank you so much for your help
As you noted, I grouped Stage...

And then produced Stage2

Thanks for your help, really appreciate it!!!

 

[Alex Blakenburg]

You're welcome. Glad I could help.