Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PbiCeo
Helper II
Helper II

Get sum values of number with a calculated column or measure

Hello everyone,


Could you please help me get sum values.

I have two tables below:
Table1

YearStatusTypeCategoryLot
2017Res01_SChina1
2017Res01_SChina1
2017Plan02_SJapan1
2017Plan02_SJapan1
2018Res01_LUSA1
2018Plan01_SCanada1
2019Res01_MIndia1
2019Res01_MIndia1
2020Plan02_LGermany1


Table 2

YearStatusTypeCategory
2017Res01_SChina
2017Res01_SChina
2017Plan02_SJapan
2017Plan02_SJapan
2018Res01_LUSA
2018Plan01_SCanada
2019Res01_MIndia
2019Res01_MIndia
2020Plan02_LGermany


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:

YearStatusTypeCategorySum
2017Res01_SChina2
2017Res01_SChina2
2017Plan02_SJapan2
2017Plan02_SJapan2
2018Res01_LUSA1
2018Plan01_SCanada1
2019Res01_MIndia2
2019Res01_MIndia2
2020Plan02_LGermany1

 

What formula should I use for DAX?

Thanks,

Vladi

 

2 REPLIES 2
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

 

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors