Forum Discussion
Impossible to create Calculated Item in pivot table
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.
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.
- My God THANK YOU! You're a lifesaver!
Have a great day!
--Ari.