I’m trying to create a table using several columns in my dataset, grouped by their common values (High Risk, Medium Risk, Low Risk).
This table should show the count of High Risk, Medium Risk and Low Risk in each column / field. If a column doesn’t have a risk level (e.g. High Risk), the table should show 0 count. I’ve attached a sample dataset and table I want to see.
Issue:
When I drag and drop multiple fields in Quicksight to create a pivot table / table, it forces the table to be hierarchical and group the data by one field, instead grouping by their common values.
My workaround and issue:
I created a separate table for each column, hid the row headers, and placed them next to one another to make a ‘table’. See attached example.
The issue is if a column doesn’t have a risk level (e.g. High Risk), the rows in that individual table shift up and don’t align with the rest. See attached example - you can see the ‘Wildfire’ values are shifted up and don’t align with the High, Medium, Low Risk headers.
My questions:
Is there a way to group these columns by common values in a table / pivot table?
If not and I use my workaround, how do I fill in the absent Risk Level counts with '0’s, so the rows don’t shift up? (e.g. If Wildfire doesn’t have High Risk, fill in that row with 0)
If I understand your problem To tackle the issue you’re facing, here’s a straightforward approach:
Create Calculated Fields for Each Risk Level: You’ll want to set up calculated fields for each risk level (High Risk, Medium Risk, Low Risk) that return ‘1’ if the risk level is present and ‘0’ otherwise. This ensures you have a ‘0’ count when a risk level isn’t present. For instance, for “High Risk”:
ifelse(RiskLevel = 'High Risk', 1, 0)
Do the same for “Medium Risk” and “Low Risk”.
Build a Table with These Calculated Fields: Once you’ve got your calculated fields, add them to your QuickSight analysis. Create a table and include these fields as measures.
Set Up the Table to Display Grouped Values:
Drag the calculated fields into the measures area of the table.
Use a category field (like risk name or another relevant field) as the row dimension.
Make sure the aggregation for the calculated fields is set to “Sum” to count the occurrences of each risk level.
Check the Display of Values:
Your table should now show a row for each category with counts for each risk level, filling in ‘0’ where a risk level isn’t present.
Adjust the Formatting:
Tweak the table formatting as needed to ensure the data is displayed clearly and aligned.
By following these steps, you should be able to create a table in QuickSight that groups common values and fills in missing counts with ‘0’, keeping your rows aligned. If this helps you, please, let me know!
Hi @yeeman,
It’s been awhile since we last heard from you, did you have any additional questions or did the solution provided above help with your case?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Hi @yeeman,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.
