Power BI relationships across common fields after pivoting columns

[Anonymous]

In Power BI, the data is formatted so that category X is the name of the column but I needed to the category name to be part of the legend so I pivoted the column after duplicating that table.

In my visual, I needed a total revenue slicer that slices across charts using the original table and the duplicated table.

I tried connecting the revenue amounts (this was not one of the columns pivoted) between the table but this is where it got funky because of the pivoting. It's a many to many cardinality and a single cross functional direction and is not active.

Any suggestions on how to move forward?

[mickey64]

Generally, the solution is to create a new table using the SUMMARIZE function and add 2 relationships.

Step 1: I make a summarize table 'category X_Index' in 'Table view'.

    category X_Index = SUMMARIZE('DATA_Table','DATA_Table'[category X])

Step 2: I add 2 relationships in 'Model view'.

    'DATA_Table'<---'category X_Index'--->'pivoted DATA_Table'