SOLVED

Calculated items issue in pivot table

Occasional Contributor

Hi all,

 

I'm having issue in calculating items in my pivot table.

I wish I could add variances and ratios and the right of my table and that it adapts whenever I change my data but it doesn't work. I did it manually but the layout changes when I change data and so I have to do it again.

Could you please help me solve that issue?

Thanks.

Here is an example of my issue with the simple formula I added.

Matthieu_Peters_0-1646127178172.png

 

11 Replies

Hi @Matthieu_Peters 

 

if I understand correctly, you put the formulas for the variances manually beside your pivot table, right?

What you should do instead, is to create so-called calculated fields within the pivot table. To do this, put the active cell on any value field in your pivot table and then open the menu "PivotTable Analyze | Calculated Field..."

 

DTE_0-1646210090776.png

There you can add new fields by creating simple formulas which are based on the existing fields. Here is a simple example:

DTE_1-1646210326211.png

This new field "Variance" can be used directly in the pivot table like any other field.

You just need to know that the possibilites are limited to very simple operations, like + - / * and you can reference only existing fields from your pivot table.

 

Hi @Martin_Weiss, thanks for the reply.

 

Unfortunately, I can't do it the way you told me. I need to compute the variance between 2 columns and it doesn't work. Here is the result I have : 

Matthieu_Peters_0-1647936092637.png

While I wish I could compute Réel - REF0. 

I don't know if it is feasible.

 

 

Hi @Matthieu_Peters 

 

actually, that's exactly what calculated fields are made for: making simple calculations between different columns.

Could you please provide more screenshots:

the list of PivotTable-fields and also what the calculated field looks like. So something like this:

DTE_0-1648020303534.png

It would be even better to have an example file with some random data uploaded, if possible.

 

Hi @Martin_Weiss 

 

Matthieu_Peters_0-1648110780148.png

And here is the formula I applied

Matthieu_Peters_1-1648111680191.png

Is this enough for you or you need more info?

 

Thanks a lot for the help

Hello @Matthieu_Peters 

 

thank you, now it's clear. You need to create a caluclated item instead.

Select one of the two column headers (Reel or BI), then goto PivotTable Analyze and select "Calculated Item..."

DTE_0-1648193295328.png

Then select the field Statut and enter the formular like this:

DTE_1-1648193410638.png

This should give the desired result, like in my example:

DTE_2-1648193446887.png

 

 

 

Hi @Martin_Weiss

 

Thanks for the reply but when I do so, this automatic message appears

Matthieu_Peters_0-1648218364298.png

 

 

Hi @Matthieu_Peters 

 

you try to insert the calculated item in the wrong field "Categorie", but it should be in the field "Statut". So, you need to first select the cells "Reel" or "BI" in your pivot table and then create the new calculated item:

DTE_0-1648447725851.png

 

@Martin_Weissthanks a lot for the help.

However I do not have the same layout as previously and all "Clé de répartition" are shown.

Matthieu_Peters_0-1648448373201.png

 

best response confirmed by Matthieu_Peters (Occasional Contributor)
Solution

@Matthieu_Peters 

 

you just need to apply a value filter. Right-click on one of the "Clé de répartition" items and select "Filter | Value Filters..."

DTE_0-1648449800527.png

 

There you can filter on values the do not equal 0:

DTE_1-1648449844219.png

 

Many thanks @Martin_Weiss 

I finally have the result I wanted

Kr

Hello @Martin_Weiss 

Hope you are doing good.

I have a final question regarding my report.

I added a % column as for the variance but when I try to remove "0" as you told me previously, it does'nt work anymore and I get that result :

Matthieu_Peters_0-1649771769691.png

Did I do something wrong?