Hi, I'm desperately trying to add a Calculated Item to a pivot table I have created in an Excel [365 for Mac] file. When I click in Fields, Items, & Sets > Calculated Item... but I get a cryptic error message that I can't make sense out of : "If one or more fields in the PivotTable have calculated items, no fields can be used in the data area two or more times, or in the data area and another area at the same time. If you are trying to add a field, remove the calculated items and add the field again. If you are trying to add a calculated item, change the PivotTable report so that no field is used more than once and then add the calculated item." I don't know what I'm doing wrong. Can someone offer any help? Do you need to see the file in question? Thanks in advance, --Ari.

AriBouaniche , HansVogelaar I'm missed, do we speak about cached PivotTable or about data model PivotTable and is the latest available on Mac? If data model we may add calculated column to the table as =VAR niv=Table1[Niv_Global] RETURN IF( niv IN {"A1 non atteint","A1","A1+"} , "Echec", "Réussite" ) (actually the same as add it was added to data source), name it it Niv_Global and use in PivotTable

AriBouaniche Yrs, it would help to see the workbook. 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?

Thanks HansVogelaar !!! Here's some data from the workbook (the data is anonymous, but I confirmed that the Calculated Item... command still returns the same error message...): https://nuage03.apps.education.fr/index.php/s/M2wLNbyiDWiwtNH What I am trying to do is, in the pivot table named Echec_Reussite (second pivot table in the "Niveau global" sheet), calculate two items using the column "Niv_global" in the "Data" sheet: I need the first item to be named "Echec" and be the sum of the counts of "A1 non atteint", "A1", "A1+", and another called "Reussite" to be the sum of the counts of "A2", "A2+", "B1", "B1+", "Au-delà de B1". My idea is, in said pivot table, to be able to display the counts of "Echec" and "Reussite" in each "Dpt". There are other columns in my complete workbook with which I mean to do this, so I would be immensely grateful for a detailed walkthrough / fix for the error! Again, many thanks for all the help you'll be able to provide... Best, --Ari.

AriBouaniche The problem is that: The first pivot table has the field Niv_Global in both the Columns area and the Values area. This prevent calculated items from being created. The second pivot table is based on the same pivot cache as the first one, so it suffers from the same limitation. To get around this: Select the second pivot table and cut it (Ctrl+X). Paste it into a new workbook. Select two or more départements. Right-click in the selection and select Group... from the context menu. Select the entire pivot table and cut it. Paste it back into the original worksheet. Right-click one of the grouped items and select Ungroup... from the context menu. The second pivot table now has its own pivot cache, so you can create calculated items.

Thanks HansVogelaar ! I can now define the Calculated Items, but I am running into another snag (I am feeling SO clueless...) : when I try to display the count of "Niv_global" (so that I can hide everything but "Echec" and "Reussite") for each of the "Dpt", I get the same error message as before, as I am now using the same field twice, which is highly unfortunate. The updated sample file is still available at the former address (https://nuage03.apps.education.fr/index.php/s/sqoRBTngoeg5XGq) So I'm guessing the workaround would be to code for a Calculated Field, which can be inserted anywhere, which I have done already, but I can't remember how I had managed to do it, and the file in question got corrupted and I cannot access the DAX formulae I had taken 4 days to come up with (I'm new to and not very at such things...) Do you have any idea as to how this could be contrived? Again, many MANY thanks for all the help you are providing! --Ari.

Impossible to create Calculated Item in pivot table

HansVogelaar
MVP
Aug 28, 2023
AriBouaniche

Yrs, it would help to see the workbook. 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?
- AriBouaniche
  Copper Contributor
  Aug 28, 2023
  Thanks HansVogelaar !!!
  
  Here's some data from the workbook (the data is anonymous, but I confirmed that the Calculated Item... command still returns the same error message...):
  https://nuage03.apps.education.fr/index.php/s/M2wLNbyiDWiwtNH
  
  What I am trying to do is, in the pivot table named Echec_Reussite (second pivot table in the "Niveau global" sheet), calculate two items using the column "Niv_global" in the "Data" sheet: I need the first item to be named "Echec" and be the sum of the counts of "A1 non atteint", "A1", "A1+", and another called "Reussite" to be the sum of the counts of "A2", "A2+", "B1", "B1+", "Au-delà de B1". My idea is, in said pivot table, to be able to display the counts of "Echec" and "Reussite" in each "Dpt".
  
  There are other columns in my complete workbook with which I mean to do this, so I would be immensely grateful for a detailed walkthrough / fix for the error!
  
  Again, many thanks for all the help you'll be able to provide...
  
  Best,
  
  --Ari.
  - HansVogelaar
    MVP
    Aug 28, 2023
    AriBouaniche
    
    The problem is that:
    
    The first pivot table has the field Niv_Global in both the Columns area and the Values area. This prevent calculated items from being created.
    
    The second pivot table is based on the same pivot cache as the first one, so it suffers from the same limitation.
    
    To get around this:
    
    Select the second pivot table and cut it (Ctrl+X).
    
    Paste it into a new workbook.
    
    Select two or more départements.
    
    Right-click in the selection and select Group... from the context menu.
    
    Select the entire pivot table and cut it.
    
    Paste it back into the original worksheet.
    
    Right-click one of the grouped items and select Ungroup... from the context menu.
    
    The second pivot table now has its own pivot cache, so you can create calculated items.

Forum Discussion

Impossible to create Calculated Item in pivot table

Share

Resources