How to get the column label in a pivot ?

**CHESINI** · 03-26-2021, 02:32 PM

Hello,

I have a pivot table with column labels YearMonth values (eg 202101, 202102) and row labels CustomerNames. The values in the table are the sales amounts.

To get the maximum value of the monthly sales I add a calculated item MaxSales as an additional column with formula =MAX('202101','202102','202103'). That works perfect and for each CustomerName row I get the MaxSales figure.

Now I try to add another additional column that contains the YearMonth of the MaxSales, in other words that contains the column label value of the month in which the MaxSales is realized.

However, I do not succeed. Is it technically possible or is this a limit of pivot tables ? As Excel knows to find the MaxSales it should know where it found it and should be able to look up and return the associated YearMonth. Does anyone knows the trick to solve this ?

Many thanks.

**alansidman** · 03-26-2021, 02:37 PM

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

**CHESINI** · 03-26-2021, 03:35 PM

Attachment added

**alansidman** · 03-26-2021, 04:19 PM

With power query, here is a solution that shows max sales by monthyear and monthyear by customer.

**alansidman** · 03-26-2021, 04:25 PM

crossposted: https://www.mrexcel.com/board/thread...pivot.1166261/

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

**CHESINI** · 03-30-2021, 08:57 AM

Does the attached file contains the solution example ? When I open it, I don't see a difference with the original.

**alansidman** · 03-30-2021, 09:24 AM

Apologize. Forgot to save file before closing and uploading. Will work on this tonight. No time during the day today.

How to get the column label in a pivot ?

LinkBack

Thread Tools

Rate This Thread

Display

How to get the column label in a pivot ?

Re: How to get the column label in a pivot ?

Re: How to get the column label in a pivot ?

Re: How to get the column label in a pivot ?

Re: How to get the column label in a pivot ?

Re: How to get the column label in a pivot ?

Re: How to get the column label in a pivot ?

Thread Information

Users Browsing this Thread

Similar Threads

How to Bring Column labels dynamically to pivot table as the column label is changed

Column label format after pivot refresh

Pivot Table Macro: Σ Values Defaults in Row Label, Need to move it to Column Label

[SOLVED] Get distinct values from Named Range, Table Column, or Pivot Table Row Label/Column Label?

Changing Pivot Column Label with Marco

Manipulating a Column Label Filter on a Pivot using VBA

Linking column label in Pivot table

Bookmarks

Bookmarks

Posting Permissions