Forum Discussion

Copper Contributor

Mar 01, 2022

Solved

Calculated items issue in pivot table

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 wor...

excel

Martin_Weiss
Mar 28, 2022
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..."

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

Martin_Weiss

Bronze Contributor

Mar 25, 2022

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..."

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

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

Matthieu_Peters

Copper Contributor

Mar 25, 2022

Hi Martin_Weiss

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

Martin_Weiss
Bronze Contributor
Mar 28, 2022
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:
- Matthieu_Peters
  Copper Contributor
  Mar 28, 2022
  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.
  - Martin_Weiss
    Bronze Contributor
    Mar 28, 2022
    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..."
    
    There you can filter on values the do not equal 0: