Create Hierarchy for Pivot

Creating a hierarchy from this kind of unpivoted data can be challenging, especially since traditional hierarchies in Power BI and Excel require structured column groupings rather than unpivoted columns. To accomplish this, consider these steps:

1. Restructure Your Table for Hierarchy:
- After unpivoting your table, add a new column for “Stress Level” by categorizing the responses into the appropriate stress range (0-3, 4-6, 7-9).
- This allows you to group by stress levels more effectively.

2. Re-pivot the Table Using Calculated Columns or Measures:
- Create a calculated column or a custom measure to capture each response and its associated stress level.
- For example, you could create a measure to calculate the count of each response per stress level.

3. Build the Hierarchy by Using a Relationship or DAX Measure:
- You can create a hierarchy structure using DAX measures in Power BI by creating separate calculated tables for each level.
- Link the response counts to each corresponding stress level, creating a virtual hierarchy in your visualizations.

4. Use Pivot Table Features in Power BI to Group the Data:
- In Power BI, place "Response" in the rows section, “Stress Level” in the columns section, and the count measure in the values section.
- This setup lets you see the hierarchy effect without explicitly creating a formal hierarchy.

Example of DAX Measures

To create calculated measures in Power BI:

1. Stress Level Count:
DAX
StressCount = COUNTAX(FILTER(Table, Table[Stress Level] = "0-3"), Table[Response])

2. Hierarchy Visualization:
Use these measures in a matrix or table visualization. Place the "Response" in the rows, and the DAX measures for each stress level in the values.