Displaying Detail data with Summary data

[thuetten]

Hey all,

I have two tables I'm working with, a detail table and a table more summarized costs. The detail table contains internal costs on a per - line level, and the summary table contains external price. I'm trying to combine the two into a matrix that shows most of the details, but I need a couple of columns from the summarized table and I'm having issues.

asdf

Detail table:

ID	ID 2	Vendor	Internal Cost
1	1	A	24
1	2	B	42
2	1	C	22

Summary table:

ID	ID2	External Price
1	1	35
1	2	50
2	1	30

The matrix I'm trying to get to is below, to where I can expand/collapse on ID and also filter by vendor.

ID	ID2	Internal Cost	External Price
1	1	24	35
1	2	42	50
2	1	22	30

I've created a many to 1 relationship between the detail and summary table based on ID and ID2. The issue I'm having is that the matrix is set up using all columns from the detail table, and when I add external price from the summary table it sums it across the entire data set so every row is showing 115, and not the sum based on ID and ID2.

If I do it in a table, it works fine, but I want the matrix expand/collapse functionality and the ability to freeze columns.

Is there something I can change to get the summary data to aggregate by ID and ID2 like it's joined upon?

[thuetten · Accepted solution]

I figured this out after looking at @VasTg's reply.

I created a new table with the distinct ID and ID2 columns, and then joined it to both the detail and the summary table. I used ID and ID2 for the rows in the matrix, and then values from both the details and summary columns worked as expected in the matrix.

[amitchandak]

Looking at your data ID, ID2 seems like a unique combination. So you can create a concatenated key and use that join 

new Key = ID & "-" & ID2.

Or in summary, you can have a new column like

Max Cost in Summary  = maxx(filter(Detail ,Summary [ID] = Detail [ID] && Summary [ID2] = Detail [ID2],Detail[	Internal Cost])

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 Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[thuetten]

@amitchandakThere's actually a couple more columns that make it distinct, but I did concatenate all of those columns and joined it that way. It worked, because there is a 1 to many relationship and it does work if I display the data above in a table. It just won't work once I switch it to a matrix.

[VasTg]

@thuetten 

I would recomment to create two tables(Dimension) for ID and ID2 with unique values as below

ID

1

2

ID2

1

2

Define relationship from ID to Detail(id) and ID to Summary(id) - 1 to Many

Define relationship from ID2 to Detail(id2) and ID2 to Summary(id2) -1 to Many 

You matrix should work after that.

If this helps, mark it as a solution

Kudos are nice too

[thuetten]

Do you mean two additional tables?