Forum Discussion
Joining 2 tables with summarization
Simple question which is extremely easy to sort in SQL but in Power BI i'm stuck.
Table1 (Parent Table)
| ID | ProductCode | TotalItems |
| 1 | 123 | 10 |
| 2 | 124 | 6 |
| 3 | 124 | 1 |
| 4 | 125 | 7 |
Table2 (Child Table Joined on ID)
| ID | ProductCode | TotalItems |
| 1 | 123 | 10 |
| 2 | 124 | 6 |
| 3 | 124 | 1 |
| 2 | 124 | 6 |
Create a simple visualisation
(Note Table 2 ID, ProductCode and TotalItems are set to 'Don't summarize' and 'Show Items with no data' as one of the records that is in Table 1 is not in Table 2)
| ID | ProductCode | TotalItems | ID | ProductCode | TotalItems |
| 1 | 123 | 10 | 1 | 123 | 10 |
| 2 | 124 | 6 | 2 | 124 | 6 |
| 3 | 124 | 1 | 3 | 124 | 1 |
| 4 | 125 | 7 |
All good so far..
Now I wan't to summarize the TotalItems so I select the 'Sum' option against Table 1 TotalItems and the TotalItems of 7 in table 1 disappears. However the Total of 24 is correct...
| ID | ProductCode | TotalItems | ID | ProductCode | TotalItems |
| 1 | 123 | 10 | 1 | 123 | 10 |
| 2 | 124 | 6 | 2 | 124 | 6 |
| 3 | 124 | 1 | 3 | 124 | 1 |
| 4 | 125 | ||||
| Total | 24 |
Does anyone no how to get the 7 to reappear??
In relationship manager properties are set as follows:
Cardinality : Many to 1
Cross filter direction : Single (I've tried setting to both)
Make this relationship active : True
Any help would be greatly appreciated.
Thanks Mark
3 Replies
- YukiK
Impactful Individual
I'm not sure what the business use case for getting everything into a table... I don't have an answer to as to why Power BI does that (wanna know if anybody else does!), but why not just putting all the columns only from Table 1 in to the table visual??
A workaround would be to add rows that table B doesn't have but table A does.
Hope this helps!
- AlexisOlson
Super User
The reason for this is that when you turn off summarize for the Table2 columns, these are now treated as dimensions and thus part of the filter context for SUM ( Table1[TotalItems] ). You can verify this by turning on summarization for all of the Table2 columns or by removing them. Since ProductCode 125 doesn't correspond to any rows in Table2, the 7 gets filtered out.
v-yanjiang-msftCommunity Support
Hi MarkArnold,
As far as my test, it's hard to achieve the output. However, you can also calculate by the following method。
If both tables have the same column names and record the same type of data, you can use Append Queries in Power Query, this is more convenient for you to analyze the data.
Select table1, then click Append Queries tab under Home ribbon.
Select Table2 in the Table to append option.
Then get all data in table1 and table2.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
