Values in Matrix shown based on different row context

[darwindat]

Hello everyone, 

I have one issue and would really appreciate any help or idea. 

I create a Matrix, based on a (simplified) dataset (can be seen in the picture below). I have 2 columns A, and B and a Value column. 

Is it possible if with only 1 column in the same Matrix - (called Column in the picture), we can create 2 measures, one showing the sum of value based on the Row context of Column A (I called this measure: Column A row context), and another showing the sum of values based on the Row context of Column B (Column B row context). The "Desired Matrix" is what I want to achieve (I did it by Excel). 
I hope it would be easier to understand my question with the below picture and my example pbix file.

Thanks in advance! 

[amitchandak · Accepted solution]

Create a dimension table

Dim = distinct(table[A])
Or
Dim = distinct(union(all(table[A]),all(table[B])))

Join it both columns A and B, One join will be active. Another one will be inactive. use userelation to activate the join in the formula for column B

How to use userelation refer :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Check for terminations

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[MFelix · Accepted solution]

Hi @darwindat ,

Create a dimension table with distinct values from the Column A and Column B then make two inactive relationships between this new table and the two columns A and B then add the following two measures:

Contex A = CALCULATE(SUM('Table'[Value]); USERELATIONSHIP('Table'[Column A];'Row Context'[Column Context]))

Contex B = CALCULATE(SUM('Table'[Value]); USERELATIONSHIP('Table'[Column B];'Row Context'[Column Context]))

Add the column from the new table as your main context on your table and filter out all the blanks.

check PBIX file attach.

[darwindat]

Thank you guys @MFelix and @amitchandak for your great help! 🙂 They are same solutions and they work! Thanks!

[amitchandak · Accepted solution]

Create a dimension table

Dim = distinct(table[A])
Or
Dim = distinct(union(all(table[A]),all(table[B])))

Join it both columns A and B, One join will be active. Another one will be inactive. use userelation to activate the join in the formula for column B

How to use userelation refer :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Check for terminations

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin