Joining 2 tables with summarization

[MarkArnold]

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

[v-yanjiang-msft]

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 _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

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.

[YukiK]

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!