Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all, do you have any ideas how to merge two table with sumif condition?
I have two tables as shown below and I would like to have a new column in TABLE 2 which in excel the new column would be sumif(TABLE1[Medium Batch Name], TABLE2[Medium Batch Name], TABLE1[record_1])
TABLE 1
TABLE 2
Solved! Go to Solution.
Here is one of the approaches to solve this given that you do not have a relationship between the two tables.
=CALCULATE( SUM(Table1[Record 1]), FILTER( Table1, Table1[Col One] = CALCULATE(VALUES(Table2[Col One])) ) )
If these tables are related in your model your calc column DAX will be simply = CALCULATE(SUM(Table1[Record 1]))
Thanks, Nick -
Hi @Charmaine,
Try this calculated column formula in Table2
=CALCULATE(SUM(Table1[Number]),FILTER(Table1,Table1[Text]=EARLIER(Table2[Text])))
Hope this helps.
Here is one of the approaches to solve this given that you do not have a relationship between the two tables.
=CALCULATE( SUM(Table1[Record 1]), FILTER( Table1, Table1[Col One] = CALCULATE(VALUES(Table2[Col One])) ) )
If these tables are related in your model your calc column DAX will be simply = CALCULATE(SUM(Table1[Record 1]))
Thanks, Nick -
Is there something that would cause = CALCULATE(SUM(Table1[Record 1])) to not function properly?
I have a smiliar situation where I have two tables. One table lists shipment numbers (Table 1 [Shipment Number]) and quantities of product on the shipment (Table 1[Qty]). The shipment numbers in this table are not unique, as there is a separate line item for each item for each item shipped in the shipment as below. What I would like to do is populate Table 2[Total Qty]. Shipment Numbers are related in a one to many relationship between the two tables. I entered the formula =CALCULATE(SUM(Table 1[Qty])) and every cell shows up as a blank. If I remove the CALCULATE function and only use sum, it sums all of the values, but appears to be unable to break them down by shipment. For example, if 34,000,000 units were shipped in all shipments, that's the number I get in all cells.
Both shipment numbers are text and the Qty column is whole number (and I tried decimal number as well) and that doesn't appear to have solved it. Any thoughts on something I might be overlooking that's preventing this from calculating?
Edit: I should note that I entered the above formula as a new column while in Table 2 and not within edit query.
Thanks
Table 1
Shipment Number | Product | Qty |
7437402 | A | 1300 |
7437402 | B | 700 |
7437402 | D | 650 |
7438014 | C | 800 |
7438014 | D | 400 |
7438093 | A | 1900 |
7438842 | D | 750 |
7438842 | C | 900 |
Table 2
Shipment Number | Total Qty |
7437402 | |
7438014 | |
7438093 | |
7438842 |
Hi,
It looks like you are writing a calculated column rather than a measure. Create a relationship from the Shipment Number column of Table1 to the Shipment Number column of Table2 (you do not need to have the Qty column in Table2). To your visual, drag the Shipment Number column from Table2 and write this measure
=SUM(Table 1[Qty])
Hope this helps.
Thank you so much, Nick.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |