Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi @Satish_Kumar,
Please try below code if it works on your side.
Sample:
Tables.
Formula:
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",LOOKUPVALUE(Sheet2[Posting Date],Sheet2[No_],Sheet1[Document No_]),"Posting Description",LOOKUPVALUE(Sheet2[Posting Description],Sheet2[No_],Sheet1[Document No_]))
Regards,
Xiaoxin Sheng
Hi @Satish_Kumar,
First of all, Summarize function is used to summary columns and return a result table. So you can't use it at calculate column. You need to use calculate table to receive the result of the function.
For example:
Base table.
I want to summary above table and get the count of product and sum of amount, formula:
Summary Table = SUMMARIZE(Sheet1,[Product],"Count",COUNTA(Sheet1[Product]),"Sum",SUM(Sheet1[Amout]))
In addition, you can directly use visual to get the summary result:
Table visual:
Matrix visual:
Reference:
Returns a summary table for the requested totals over a set of groups.
Regards,
Xiaoxin Sheng
HI TO ALL ...
THANXX FOR HELP...
BUT I AM ASKING FOR GROUPING AND JOINNING OF TWO TABLE TO MAKE A COMMAN TABLE...
GROUPING OF TABLE IS DONE SO PLEASE HELP ME IN JOIN ASAP.
Hi @Satish_Kumar,
If you share us some sample data the requirement will be more clearly.
For join two table, you can use NATURALINNERJOIN,NATURALLEFTOUTERJOIN. You can also use SelectColumns to choose different columns from different tables.
Regards,
Xiaoxin Sheng
i am sending you the demo data for this issue.
Document No_ | Buy-from Vendor No_ | Posting Group | Amount Including Tax |
PINV/0809/00001 | VEN0027 | FINISHED | 220895 |
PINV/0809/00002 | VEN0027 | FINISHED | 625408 |
PINV/0809/00003 | VEN0027 | FINISHED | 33098 |
PINV/0809/00004 | VEN0004 | FINISHED | 322341 |
PINV/0809/00005 | VEN0004 | FINISHED | 3961 |
PINV/0809/00006 | VEN0004 | 49838 | |
PINV/0809/00006 | VEN0004 | 0.02 | |
PINV/0809/00007 | VEN0027 | FINISHED | 15912 |
PINV/0809/00008 | VEN0027 | FINISHED | 336177 |
PINV/0809/00009 | VEN0027 | FINISHED | 710793 |
PINV/0809/00010 | VEN0004 | FINISHED | 2154058 |
PINV/0809/00011 | VEN0006 | FINISHED | 23204 |
PINV/0809/00012 | VEN0006 | FINISHED | 946444 |
PINV/0809/00013 | VEN0004 | 191290 | |
PINV/0809/00013 | VEN0004 | -0.45 | |
PINV/0809/00014 | VEN0039 | 48687 | |
PINV/0809/00015 | VEN0040 | COMPUTERS | 30720 |
PINV/0809/00016 | VEN0041 | OFFICE EQU | 1260 |
PINV/0809/00016 | VEN0041 | OFFICE EQU | 1500 |
PINV/0809/00017 | VEN0013 | UPS & BATT | 73500 |
PINV/0809/00018 | VEN0010 | FINISHED | 22781 |
PINV/0809/00018 | VEN0010 | FINISHED | 5923 |
PINV/0809/00019 | VEN0004 | FINISHED | 1057044.34 |
PINV/0809/00019 | VEN0004 | -0.34 | |
PINV/0809/00020 | VEN0004 | FINISHED | 8225 |
PINV/0809/00021 | VEN0004 | 147258 | |
PINV/0809/00021 | VEN0004 | -0.09 | |
PINV/0809/00022 | VEN0027 | FINISHED | 301702.91 |
PINV/0809/00022 | VEN0027 | 0.09 | |
PINV/0809/00023 | VEN0027 | FINISHED | 39671.1 |
PINV/0809/00023 | VEN0027 | -0.1 | |
PINV/0809/00024 | VEN0027 | FINISHED | 678730.08 |
PINV/0809/00024 | VEN0027 | -0.08 | |
PINV/0809/00025 | VEN0027 | FINISHED | 787962.96 |
PINV/0809/00025 | VEN0027 | 0.04 | |
PINV/0809/00026 | VEN0027 | FINISHED | 229900.19 |
PINV/0809/00026 | VEN0027 | -0.19 | |
PINV/0809/00027 | VEN0027 | FINISHED | 131541.53 |
PINV/0809/00027 | VEN0027 | 0.47 | |
PINV/0809/00028 | VEN0006 | FINISHED | 5886013.26 |
PINV/0809/00028 | VEN0006 | -0.26 | |
PINV/0809/00029 | VEN0004 | 20025 | |
PINV/0809/00029 | VEN0004 | -0.09 | |
PINV/0809/00030 | VEN0004 | FINISHED | 143743.03 |
PINV/0809/00030 | VEN0004 | -0.03 | |
PINV/0809/00031 | VEN0004 | 15416 | |
PINV/0809/00031 | VEN0004 | -0.42 | |
PINV/0809/00032 | VEN0004 | FINISHED | 110657.55 |
PINV/0809/00032 | VEN0004 | 0.45 | |
PINV/0809/00033 | VEN0018 | COMPUTERS | 30000 |
PINV/0809/00034 | VEN0043 | COMPUTERS | 23500 |
PINV/0809/00035 | VEN0004 | FINISHED | 148066.35 |
PINV/0809/00036 | VEN0044 | FURNITURE | 167562 |
PINV/0809/00037 | VEN0004 | FINISHED | 180676.66 |
PINV/0809/00037 | VEN0004 | 200 | |
PINV/0809/00038 | VEN0010 | FINISHED | 5923 |
PINV/0809/00038 | VEN0010 | 22781 | |
PINV/0809/00039 | VEN0045 | 37079 | |
PINV/0809/00040 | VEN0004 | 15416 | |
PINV/0809/00040 | VEN0004 | -0.42 | |
PINV/0809/00040 | VEN0004 | 0.42 | |
PINV/0809/00041 | VEN0004 | 20025 | |
PINV/0809/00041 | VEN0004 | -0.09 | |
PINV/0809/00041 | VEN0004 | 0.09 | |
PINV/0809/00042 | VEN0004 | FINISHED | 110657.55 |
PINV/0809/00042 | VEN0004 | 0.45 | |
PINV/0809/00042 | VEN0004 | -0.45 | |
PINV/0809/00043 | VEN0004 | FINISHED | 143743.03 |
@v-shex-msft this 2nd table.
No_ | Buy-from Vendor No_ | Posting Date | Posting Description |
PINV/0809/00001 | VEN0027 | 4/15/2008 0:00 | Order PO00001 |
PINV/0809/00002 | VEN0027 | 4/15/2008 0:00 | Order PO00002 |
PINV/0809/00003 | VEN0027 | 4/21/2008 0:00 | Order PO00003 |
PINV/0809/00004 | VEN0004 | 4/22/2008 0:00 | Order PO00004 |
PINV/0809/00005 | VEN0004 | 4/22/2008 0:00 | Order PO00005 |
PINV/0809/00006 | VEN0004 | 4/22/2008 0:00 | Order PO00006 |
PINV/0809/00007 | VEN0027 | 5/7/2008 0:00 | Order PO00007 |
PINV/0809/00008 | VEN0027 | 5/7/2008 0:00 | Order PO00008 |
PINV/0809/00009 | VEN0027 | 5/8/2008 0:00 | Order PO00009 |
PINV/0809/00010 | VEN0004 | 5/31/2008 0:00 | Order PO00010 |
PINV/0809/00011 | VEN0006 | 5/29/2008 0:00 | Order PO00012 |
PINV/0809/00012 | VEN0006 | 5/29/2008 0:00 | Order PO00013 |
PINV/0809/00013 | VEN0004 | 5/12/2008 0:00 | Order PO00011 |
PINV/0809/00014 | VEN0039 | 4/17/2008 0:00 | Order PO00015 |
PINV/0809/00015 | VEN0040 | 6/1/2008 0:00 | Order PO00016 |
PINV/0809/00016 | VEN0041 | 5/14/2008 0:00 | Order PO00017 |
PINV/0809/00017 | VEN0013 | 5/12/2008 0:00 | Order PO00018 |
PINV/0809/00018 | VEN0010 | 6/29/2008 0:00 | Order PO00019 |
PINV/0809/00019 | VEN0004 | 7/7/2008 0:00 | Order PO00020 |
PINV/0809/00020 | VEN0004 | 7/7/2008 0:00 | Order PO00021 |
PINV/0809/00021 | VEN0004 | 7/7/2008 0:00 | Order PO00022 |
PINV/0809/00022 | VEN0027 | 6/27/2008 0:00 | Order PO00023 |
PINV/0809/00023 | VEN0027 | 6/25/2008 0:00 | Order PO00024 |
PINV/0809/00024 | VEN0027 | 6/27/2008 0:00 | Order PO00025 |
PINV/0809/00025 | VEN0027 | 6/27/2008 0:00 | Order PO00026 |
PINV/0809/00026 | VEN0027 | 6/11/2008 0:00 | Order PO00027 |
PINV/0809/00027 | VEN0027 | 7/14/2008 0:00 | Order PO00028 |
PINV/0809/00028 | VEN0006 | 7/11/2008 0:00 | Order PO00029 |
PINV/0809/00029 | VEN0004 | 7/11/2008 0:00 | Order PO00030 |
PINV/0809/00030 | VEN0004 | 7/11/2008 0:00 | Order PO00031 |
PINV/0809/00031 | VEN0004 | 7/11/2008 0:00 | Order PO00032 |
PINV/0809/00032 | VEN0004 | 7/11/2008 0:00 | Order PO00033 |
PINV/0809/00033 | VEN0018 | 7/29/2008 0:00 | Order PO00035 |
PINV/0809/00034 | VEN0043 | 6/20/2008 0:00 | Order PO00036 |
PINV/0809/00035 | VEN0004 | 8/8/2008 0:00 | Order PO00038 |
PINV/0809/00036 | VEN0044 | 1/1/2009 0:00 | Order PO00039 |
PINV/0809/00037 | VEN0004 | 7/24/2008 0:00 | Order PO00040 |
PINV/0809/00038 | VEN0010 | 7/12/2008 0:00 | Order PO00034 |
PINV/0809/00039 | VEN0045 | 7/30/2008 0:00 | Order PO00043 |
@v-shex-msft on the basis of No_ feild i have to join these table together to summerize the amount feild.
Hi @Satish_Kumar,
Please try below code if it works on your side.
Sample:
Tables.
Formula:
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",LOOKUPVALUE(Sheet2[Posting Date],Sheet2[No_],Sheet1[Document No_]),"Posting Description",LOOKUPVALUE(Sheet2[Posting Description],Sheet2[No_],Sheet1[Document No_]))
Regards,
Xiaoxin Sheng
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 @Satish_Kumar,
Did you create the relationship between these tables?
Regards,
Xiaoxin Sheng
HI TO ALL ...
THANXX FOR HELP...
BUT I AM ASKING FOR GROUPING AND JOINNING OF TWO TABLE TO MAKE A COMMAN TABLE...
GROUPING OF TABLE IS DONE SO PLEASE HELP ME IN JOIN ASAP.
@Satish_Kumar In power bi desktop go to query editor (Edit Queries) and under Transform tab use Group By. May be easier to do it this way.
but the impact was not shown in the view .
And if you share your knowledge to join two table and convert into one comman table.