How to use group's in data

Anonymous 

HI..

The solution was great but if you provide the syntax of joining two table on the basis of common feild to make a resultant table then its helpful for me.

Hi Satish_Kumar,

For this solution, I use addcolumns function and lookupvalue function to link two tables.

Comment:

Below part is use to summary table by "Document No_": SUMMARIZE(Sheet1,Sheet1[Document No_],Sheet1[Buy-from Vendor No_],"Sum of No",SUMX(FILTER(ALL(Sheet1),Sheet1[Document No_]=EARLIER(Sheet1[Document No_])),Sheet1[Amount Including Tax]))

AddColumns is used to add columns which are match current columns by lookupvalue function.

In addition, you can also use related function if you have create the relationship bwtween these tables.(you need to create relationship first,e.g. table1[Document No_] to table2[No_])

Summary Table = ADDCOLUMNS(SUMMARIZE(Sheet1,Sheet1[Document No_],Sheet1[Buy-from Vendor No_],"Sum of No",SUMX(FILTER(ALL(Sheet1),Sheet1[Document No_]=EARLIER(Sheet1[Document No_])),Sheet1[Amount Including Tax])),"Posting Date",RELATED(Sheet2[Posting Date]),"Posting Description",RELATED(Sheet2[Posting Description))

Use NATURALLEFTOUTERJOIN and SUMMARIZE function to merge tables:

Table = SUMMARIZE( NATURALLEFTOUTERJOIN(Sheet1,ALLEXCEPT(Sheet2,Sheet2[Buy-from Vendor No_])),[Document No_],[Buy-from Vendor No_],[Posting Date],[Posting Description],"Sum",SUM(Sheet1[Amount Including Tax]))

Regards,

Xiaoxin Sheng

Hi Anonymous 

i tried the solution that u provided by you but its shows an error.

Hi Satish_Kumar,

Did you create the relationship between these tables?

Regards,

Xiaoxin Sheng