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 everyone,
Could you please help me get sum values.
I have two tables below:
Table1
Year | Status | Type | Category | Lot |
2017 | Res | 01_S | China | 1 |
2017 | Res | 01_S | China | 1 |
2017 | Plan | 02_S | Japan | 1 |
2017 | Plan | 02_S | Japan | 1 |
2018 | Res | 01_L | USA | 1 |
2018 | Plan | 01_S | Canada | 1 |
2019 | Res | 01_M | India | 1 |
2019 | Res | 01_M | India | 1 |
2020 | Plan | 02_L | Germany | 1 |
Table 2
Year | Status | Type | Category |
2017 | Res | 01_S | China |
2017 | Res | 01_S | China |
2017 | Plan | 02_S | Japan |
2017 | Plan | 02_S | Japan |
2018 | Res | 01_L | USA |
2018 | Plan | 01_S | Canada |
2019 | Res | 01_M | India |
2019 | Res | 01_M | India |
2020 | Plan | 02_L | Germany |
I want to add a calculated column or measure to Table2 with sum values like below "Sum" where values columns match each other between Table1 and Table2:
Year | Status | Type | Category | Sum |
2017 | Res | 01_S | China | 2 |
2017 | Res | 01_S | China | 2 |
2017 | Plan | 02_S | Japan | 2 |
2017 | Plan | 02_S | Japan | 2 |
2018 | Res | 01_L | USA | 1 |
2018 | Plan | 01_S | Canada | 1 |
2019 | Res | 01_M | India | 2 |
2019 | Res | 01_M | India | 2 |
2020 | Plan | 02_L | Germany | 1 |
What formula should I use for DAX?
Thanks,
Vladi
Hi @PbiCeo
Try this for a calc column in Table2:
New Column =
CALCULATE(SUM(Table1[Lot]),
TREATAS(CALCULATETABLE(SUMMARIZE(Table2, Table2[Year], Table2[Status],Table2[Type], Table2[Category])),
Table1[Year], Table1[Status], Table1[Type], Table1[Category])
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@PbiCeo , Create a new column at
sumx(filter(Table1, table1[Year] =table2[Year] && table1[Status] table2[Status] && table1[ Type] =table2[Type] && table1[Category] =table2[Category] ),table1[Lot])
Refer this video : https://www.youtube.com/watch?v=czNHt7UXIe8
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 |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
71 | |
49 | |
45 | |
20 | |
17 |