SOLVED

Pivot Table Turnover 23-22

Copper Contributor

Hello together,
I have the following problem. I have sales figures of my company displayed in a pivot table, based on the year. Unfortunately, I only have one field in which the turnover is displayed, which is why it is not possible for me to create a difference between year 2023 and year 2022. Is there a way to create a difference, possibly using an auxiliary table, etc.? See also the screenshots. Unfortunatly I can not get the data based on the year because I just get every sales line from our system with a certain buyer and date where it has been bought. 

carlstegen_0-1691663034670.png

This is the Pivot table where a Sum is drawn based on the Item and the year.

10 Replies

@carlstegen 

Click on 2022 or 2023.

On the PivotTable analyze tab of the ribbon, click Fields, Items & Sets > Calculated Item...

In the Name box, enter Difference or something similar.

In the Formula box, enter =Year[2023]-Year[2022]

(This syntax is necessary because 2022 and 2023 are numbers. If you entered =2023-2022, the result would be 1)

Click Add, then click OK.

@HansVogelaar Hi and thanks for the quick reply. Unfortunatly Excel can't address the Year field. I get the following error:

carlstegen_0-1691746051050.png

 

@carlstegen 

Does it work if you use

 

=Jahr[2.023]-Jahr[2.022]

Good morning Hans,

no it doesn't work. Either I get the error that it can't resolve the Year or if I put it in "" I get a Value Error because it is seen as Text. I don't get why Excel has a SUM function but not a difference function...

@carlstegen 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

I have the example attached to the original post.
best response confirmed by carlstegen (Copper Contributor)
Solution

@carlstegen 

This worked for me: ='2023'-'2022'

HansVogelaar_0-1692003495144.png

I have attached the workbook, but you'll want to create the formula yourself, since the labels were changed to English in my version.

Note that I turned off the grand total for rows, because Excel would include the Differenz value in the grand total.

Thanks alot Hans! You are the best!
One last question: I think I found what seems to be the issue for me. I created the formula in a calculated field. However, in the excel example which you gave me you created a calculated element. How did you do that? Because under the Pivotheader Fields, Elements and Groups I can not select the option "Elements".

@carlstegen 

I clicked on 2023 (2022 would have been OK too), then selected Fields, Items & Sets > Calculated Item...

1 best response

Accepted Solutions
best response confirmed by carlstegen (Copper Contributor)
Solution

@carlstegen 

This worked for me: ='2023'-'2022'

HansVogelaar_0-1692003495144.png

I have attached the workbook, but you'll want to create the formula yourself, since the labels were changed to English in my version.

Note that I turned off the grand total for rows, because Excel would include the Differenz value in the grand total.

View solution in original post