I have a dataset with the following columns: Name, Program Name, Assessment Name, Stage, Score, and Report. The number of assessments varies by program—some programs may include 2 or 3 assessments, while others may have all available assessments. Each assessment consists of 3 distinct stages.
The goal is to generate a pivot table with Name and Program Name as row labels, and Assessment Name with its 3 stages as column headers. The Score (numeric value) and Report (string value) should be displayed in their corresponding assessment and stage columns. A key challenge is that the Report is a string, and it’s essential to ensure that both Score and Report values are properly aligned with their specific assessments and stages, avoiding any misplacement in the table.
Currently, when we add both values in the pivot, they are applied to all columns, but we need them to appear only in their respective columns. Additionally, the string data (Report) is being treated as a count, which isn’t the desired outcome.
The main requirements are as follows:
Visualization of string data in the pivot table without it being treated as a count.
Proper placement of two measures (Score and Report) in the values section, ensuring they are added to their corresponding columns.
Attached is an example of how the current pivot table looks, and I have outlined the desired structure.
Value in a pivot table can only shows number and not able to show string. Therefore, it will count the Report automatically. At AWS, our roadmap is primarily driven by our customers. Your feedback helps us build a better service. I have tagged this as a feature request.
Another thought, did you try using table? Understand it is different but it is the most similar option I can think of Uploading: image.png…
I have an another question related to the placement of measures in a pivot table which I have already mentioned above.
Currently, when we add two measures (e.g., Score and Report) to the Values section, they are automatically applied across all columns. However, our requirement is to have each measure appear only within its respective column, ensuring a clean, organized layout.
Could you please advise on how we can configure the pivot table so that Score and Report are displayed in their designated columns without overlapping into others?
Hi @DeepikaChoda,
Please check out the attached arena view for a potential work around. While this may not be exactly what you’re trying to accomplish, this would be the best option in this case.
