Create Hierarchy for Pivot

[gingersnap]

I have a table with 132 response columns. under each column is # of times each response was selected.  There is another column that has a measure of stress associated with each response. 

EX: ask buy  sell  stress

        2    3      1    0-3

       3     1     2     4-6

I unpivot so I get the following:

    response   0-3    4-6    7-9

       ask            2

       ask                    3

       buy           3       

       buy                    1

       sell            1

       sell                     2

       sell            1

       sell            2 

I would like to create a hierarchy. I tried in the raw data before unpivoting but the hierarchy doesnt stick. I cant create it in the final table because its all 1 column name. Is this possible?

[FarhanJeelani]

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.

[gingersnap]

Thank you very much for replying. Your solution was over my knowledge of PowerBI. I think I may have mislead you on what I need. 

In my origin table, each response is a separate column (156 of them) with counts in them. The stress levels, 0 to 3, 4 6, 7 to 9,10 are kept in 1 column titled scoredistribution. I unpivot all the response columns. Then I pivot the scoredistribution column to get 4 new columns.

Ex: 

 response   0-3    4-6    7-9

       ask            2

       ask2                    3

       buy           3       

       buy2                    1

       sell            1

       sell2                     2

       sell 3           1

       sell 4           2 

I want it to look like below:

 response   0-3    4-6    7-9

     +  ask            2

           ask2                    3

       +buy           3       

          buy 2                   1

       +sell            1

            sell2                     2

            sell3            1

            sell4            2 

Is this possible

[gingersnap]

That is almost what I need! Thank you for replying. 

The only difference is where you have the 0-3 under ASK I need the 0-3 still as one of the columns and all of the ASKs under the ASK . 

EX: 

   response   0-3    4-6    7-9

     +  ask            2

           ask2                    3

       +buy           3       

          buy 2                   1

       +sell            1

            sell2                     2

            sell3            1

            sell4            2 

Since ASK ASK2 BUY BUY2 SELL etc  are now all under the same column, I dont know how to build a hierarchy. 

Should I send you my PBIx?

I

[Ritaf1983]

Hi @gingersnap 
If I understood you correctly you need one more unpivot :

Then you can create visuals with the wanted hierarchies :

The pbix is attached

If it is something else please show the desired result.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly