Forum Discussion
Displaying Detail data with Summary data
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?
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.
5 Replies
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
Do you mean two additional tables?
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 LinkedinamitchandakThere'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.
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.
