Forum Discussion
Impossible to create Calculated Item in pivot table
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.De rien!
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.