In a pivot table, I’d like to create a calculated field to use in “Value” that shows something like “20 of 25 (80%)”, where 25 is based on the count of rows.
Is there a way to achieve this?
Example:
I have Continent, Country, and City as row fields.
I add my calculated filed as “value“
At the City level, it should display: 20 of 1 (X%)
At the Country level, it should display: 20 of [number of cities] (X%)
At the Continent level, it should display: 20 of [number of countries] (X%)
Hi @igfasouza,
Before diving too deep into the calculations to set this up, one point to note is that to create a value like ‘X of X (%)’, it would need to be put together using the concat function, which would create a string. When using a string field in the value section of a pivot table, the only way to aggregate or show the values would be by ‘count’ or ‘count distinct’, neither of which would be accurate to what you’d like to show so there is currently no way to portray that value format in the value section of a pivot table.
In regards to building out that calculation though; you would need a few steps to build this out. First you would need to build a rank function for each city (I’m not sure if you’d be ranking them alphabetically or based on some other value?). You would need to repeat this process for the rankings against country and continent as well.
You’ll also need to create calculated fields that will count the number of cities, countries, etc. for the second part (20 out of ‘X’).
Then you can build a concat function that combines these values, but again, would need to make one for each level.
You can try to put together in an ifelse statement that handles which concat value to show based on the row level so that you’re not displaying 3 separate columns.
Hi, @igfasouza. Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Community Q&A Forum!