Impossible to create Calculated Item in pivot table

[AriBouaniche]

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.

[#2]

@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?

[#3]

Thanks @Hans Vogelaar !!!

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.

[#4]

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

[#5]

Thanks @Hans Vogelaar !

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.

[#6]

@AriBouaniche 

You can use UAI as the Values field:

Result:

[#7]

My God THANK YOU! You're a lifesaver!

Have a great day!

--Ari.

[#8]

@AriBouaniche 

De rien!

[#9]

@Hans VogelaarI am so very sorry to bother you again... :S

I followed your advice and everything works fine, but I seem to be hitting a limitation inherent to the "no same field present twice in the same pivot table" that seems to be part and parcel of how Excel processes calculated items (which I have understood thanks to you) or seems to have to do with the fact that "Echec" and "Reussite" are items, not fields, depending on which way I try to tackle the problem.

Here's the deal: I have another pivot table with rows listing "UAI", and columns listing "Type" (which can be one of three possibilities): "Hors EP", "REP", and "REP+". What I would want to see is the number of "Echec" and "Reussite" for each UAI (or, if I'm being honest, the rate of "Echec" and/or "Reussite", which would be ["Reussite" / ("Reussite" + "Echec") * 100]). There's the snag:

- If, in value, I try to put "Niv_Global" (of which "Echec" and "Reussite" are part), I get a count of the whole field, and I cannot choose to display only some of its parts (here, the numbers for "Echec" or "Reussite").

- If, on the other hand, I put "Niv_Global" as a second column header, so that I can filter on "Reussite" only, and try to put "Niv_global" in the "Values" box, I get the error message (field used twice). If I try to put something else in the "Values" box, Excel goes on the fritz (there are, in my real file, other column and row headers on top/under those that —I think— end up making it too calculation-intensive for poor Excel... not my computer's fault, which is a brand new M2 Max MacBook Pro 16" with 96 Gb of RAM!)

My question is the following: would it be possible to calculate "Echec" and "Reussite" as Calculated Fields which are more "autonomous" and allow for much more freedom of movement within various pivot tables?

This is the DAX formula I'd come up with last year, and lost when my file got corrupted :'(

I tried again this year, but I can't figure out what I'd done last year (it had taken me forever to solve that mystery). I tried, for "Echec" the following formula: =SUM(COUNTA(Niv_Global, FILTER(ALL, Niv_Global='A1 non atteint')), COUNTA(Niv_Global, FILTER(ALL, Niv_Global='A1')), COUNTA(Niv_Global, FILTER(ALL, Niv_Global='A1+'))), but I get all sorts of nondescript error messages, and the field won't save, and there seems to be very little documentation on the Internet as to how to do this properly (it doesn't help that DAX is used in both PowerBI and Excel, it seems, but with slight syntax variations (double or single quotes?!), else I don't know what I'm doing wrong...)

Again, could you please please pretty please shed some light on this?

Thanks a million,

-- Ari.

[#10]

Another DAX formula I tried, and which doesn't work either is: "=CALCULATE(SUM( NivGlobal), FILTER( NivGlobal, NivGlobal = NivGlobal['A1 non atteint']))" and so on (including "A1" and "A1+" as well). No luck...

[#11]

@AriBouaniche 

I don't know anything about DAX, but there are others here who do.

@Sergei Baklan

@Riny_van_Eekelen 

But perhaps it'd be easier to compute Echec/Réussite in the source data, so that you don't have to use calculated items. See the attached workbook.

[#12]

@AriBouaniche ,  @Hans Vogelaar 

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

[#13]

@Sergei Baklan 

Thanks, Sergei!

[#14]

Yes, thank you indeed @Sergei Baklan, and thanks again @Hans Vogelaar for hitting up the right person!