Impossible to create Calculated Item in pivot table

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

SergeiBaklan 

Thanks, Sergei!

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 

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

SergeiBaklan

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.

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