Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |