Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Satish_Kumar
Helper V
Helper V

How to use group's in data

‎10-21-2016 12:00 AM

Help me in using Summerize command in DAX.

 

i am new at power bi.biii.png

Message 1 of 16
1,555 Views
0
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support
In response to Satish_Kumar

‎10-25-2016 06:57 PM

Hi @Satish_Kumar,

 

Please try below code if it works on your side.

 

Sample:

 

Tables.

Capture.PNGCapture2.PNG

 

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_]))

 

Capture3.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Message 12 of 16
1,770 Views
0
15 REPLIES 15
v-shex-msft
Community Support
Community Support

‎10-25-2016 12:19 AM

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.

Capture.PNG

 

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]))

 

Capture2.PNG

 

In addition, you can directly use visual to get the summary result:
Table visual:

Capture3.PNG

 

Matrix visual:

Capture4.PNG

 

Reference:

SUMMARIZE Function (DAX)

Returns a summary table for the requested totals over a set of groups.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Message 5 of 16
1,504 Views
0
Satish_Kumar
Helper V
Helper V
In response to v-shex-msft

‎10-25-2016 12:28 AM

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.

Message 7 of 16
1,500 Views
0
v-shex-msft
Community Support
Community Support
In response to Satish_Kumar

‎10-25-2016 12:31 AM

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Message 8 of 16
1,496 Views
0
Satish_Kumar
Helper V
Helper V
In response to v-shex-msft

‎10-25-2016 02:25 AM

i am sending you the demo data for this issue.

 

 

Document No_Buy-from Vendor No_Posting GroupAmount Including Tax
PINV/0809/00001VEN0027FINISHED220895
PINV/0809/00002VEN0027FINISHED625408
PINV/0809/00003VEN0027FINISHED33098
PINV/0809/00004VEN0004FINISHED322341
PINV/0809/00005VEN0004FINISHED3961
PINV/0809/00006VEN0004 49838
PINV/0809/00006VEN0004 0.02
PINV/0809/00007VEN0027FINISHED15912
PINV/0809/00008VEN0027FINISHED336177
PINV/0809/00009VEN0027FINISHED710793
PINV/0809/00010VEN0004FINISHED2154058
PINV/0809/00011VEN0006FINISHED23204
PINV/0809/00012VEN0006FINISHED946444
PINV/0809/00013VEN0004 191290
PINV/0809/00013VEN0004 -0.45
PINV/0809/00014VEN0039 48687
PINV/0809/00015VEN0040COMPUTERS30720
PINV/0809/00016VEN0041OFFICE EQU1260
PINV/0809/00016VEN0041OFFICE EQU1500
PINV/0809/00017VEN0013UPS & BATT73500
PINV/0809/00018VEN0010FINISHED22781
PINV/0809/00018VEN0010FINISHED5923
PINV/0809/00019VEN0004FINISHED1057044.34
PINV/0809/00019VEN0004 -0.34
PINV/0809/00020VEN0004FINISHED8225
PINV/0809/00021VEN0004 147258
PINV/0809/00021VEN0004 -0.09
PINV/0809/00022VEN0027FINISHED301702.91
PINV/0809/00022VEN0027 0.09
PINV/0809/00023VEN0027FINISHED39671.1
PINV/0809/00023VEN0027 -0.1
PINV/0809/00024VEN0027FINISHED678730.08
PINV/0809/00024VEN0027 -0.08
PINV/0809/00025VEN0027FINISHED787962.96
PINV/0809/00025VEN0027 0.04
PINV/0809/00026VEN0027FINISHED229900.19
PINV/0809/00026VEN0027 -0.19
PINV/0809/00027VEN0027FINISHED131541.53
PINV/0809/00027VEN0027 0.47
PINV/0809/00028VEN0006FINISHED5886013.26
PINV/0809/00028VEN0006 -0.26
PINV/0809/00029VEN0004 20025
PINV/0809/00029VEN0004 -0.09
PINV/0809/00030VEN0004FINISHED143743.03
PINV/0809/00030VEN0004 -0.03
PINV/0809/00031VEN0004 15416
PINV/0809/00031VEN0004 -0.42
PINV/0809/00032VEN0004FINISHED110657.55
PINV/0809/00032VEN0004 0.45
PINV/0809/00033VEN0018COMPUTERS30000
PINV/0809/00034VEN0043COMPUTERS23500
PINV/0809/00035VEN0004FINISHED148066.35
PINV/0809/00036VEN0044FURNITURE167562
PINV/0809/00037VEN0004FINISHED180676.66
PINV/0809/00037VEN0004 200
PINV/0809/00038VEN0010FINISHED5923
PINV/0809/00038VEN0010 22781
PINV/0809/00039VEN0045 37079
PINV/0809/00040VEN0004 15416
PINV/0809/00040VEN0004 -0.42
PINV/0809/00040VEN0004 0.42
PINV/0809/00041VEN0004 20025
PINV/0809/00041VEN0004 -0.09
PINV/0809/00041VEN0004 0.09
PINV/0809/00042VEN0004FINISHED110657.55
PINV/0809/00042VEN0004 0.45
PINV/0809/00042VEN0004 -0.45
PINV/0809/00043VEN0004FINISHED143743.03
Message 9 of 16
1,486 Views
0
Satish_Kumar
Helper V
Helper V
In response to Satish_Kumar

‎10-25-2016 02:27 AM

@v-shex-msft  this 2nd table.

No_Buy-from Vendor No_Posting DatePosting Description
PINV/0809/00001VEN00274/15/2008 0:00Order PO00001
PINV/0809/00002VEN00274/15/2008 0:00Order PO00002
PINV/0809/00003VEN00274/21/2008 0:00Order PO00003
PINV/0809/00004VEN00044/22/2008 0:00Order PO00004
PINV/0809/00005VEN00044/22/2008 0:00Order PO00005
PINV/0809/00006VEN00044/22/2008 0:00Order PO00006
PINV/0809/00007VEN00275/7/2008 0:00Order PO00007
PINV/0809/00008VEN00275/7/2008 0:00Order PO00008
PINV/0809/00009VEN00275/8/2008 0:00Order PO00009
PINV/0809/00010VEN00045/31/2008 0:00Order PO00010
PINV/0809/00011VEN00065/29/2008 0:00Order PO00012
PINV/0809/00012VEN00065/29/2008 0:00Order PO00013
PINV/0809/00013VEN00045/12/2008 0:00Order PO00011
PINV/0809/00014VEN00394/17/2008 0:00Order PO00015
PINV/0809/00015VEN00406/1/2008 0:00Order PO00016
PINV/0809/00016VEN00415/14/2008 0:00Order PO00017
PINV/0809/00017VEN00135/12/2008 0:00Order PO00018
PINV/0809/00018VEN00106/29/2008 0:00Order PO00019
PINV/0809/00019VEN00047/7/2008 0:00Order PO00020
PINV/0809/00020VEN00047/7/2008 0:00Order PO00021
PINV/0809/00021VEN00047/7/2008 0:00Order PO00022
PINV/0809/00022VEN00276/27/2008 0:00Order PO00023
PINV/0809/00023VEN00276/25/2008 0:00Order PO00024
PINV/0809/00024VEN00276/27/2008 0:00Order PO00025
PINV/0809/00025VEN00276/27/2008 0:00Order PO00026
PINV/0809/00026VEN00276/11/2008 0:00Order PO00027
PINV/0809/00027VEN00277/14/2008 0:00Order PO00028
PINV/0809/00028VEN00067/11/2008 0:00Order PO00029
PINV/0809/00029VEN00047/11/2008 0:00Order PO00030
PINV/0809/00030VEN00047/11/2008 0:00Order PO00031
PINV/0809/00031VEN00047/11/2008 0:00Order PO00032
PINV/0809/00032VEN00047/11/2008 0:00Order PO00033
PINV/0809/00033VEN00187/29/2008 0:00Order PO00035
PINV/0809/00034VEN00436/20/2008 0:00Order PO00036
PINV/0809/00035VEN00048/8/2008 0:00Order PO00038
PINV/0809/00036VEN00441/1/2009 0:00Order PO00039
PINV/0809/00037VEN00047/24/2008 0:00Order PO00040
PINV/0809/00038VEN00107/12/2008 0:00Order PO00034
PINV/0809/00039VEN00457/30/2008 0:00Order PO00043
Message 10 of 16
1,485 Views
0
Satish_Kumar
Helper V
Helper V
In response to Satish_Kumar

‎10-25-2016 02:27 AM

@v-shex-msft on the basis of No_ feild i have to join these table together to summerize the amount feild.

Message 11 of 16
1,201 Views
0
v-shex-msft
Community Support
Community Support
In response to Satish_Kumar

‎10-25-2016 06:57 PM

Hi @Satish_Kumar,

 

Please try below code if it works on your side.

 

Sample:

 

Tables.

Capture.PNGCapture2.PNG

 

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_]))

 

Capture3.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Message 12 of 16
1,771 Views
0
Satish_Kumar
Helper V
Helper V
In response to v-shex-msft

‎10-25-2016 10:07 PM

@v-shex-msft 

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.

Message 13 of 16
1,191 Views
0
v-shex-msft
Community Support
Community Support
In response to Satish_Kumar

‎10-25-2016 11:01 PM

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Message 14 of 16
1,185 Views
0
Satish_Kumar
Helper V
Helper V
In response to v-shex-msft

‎10-25-2016 11:45 PM

Hi @v-shex-msft 

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

 

error.png 

Message 15 of 16
1,182 Views
0
v-shex-msft
Community Support
Community Support
In response to Satish_Kumar

‎10-26-2016 12:02 AM

Hi @Satish_Kumar,

 

Did you create the relationship between these tables?

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Message 16 of 16
1,171 Views
0
Satish_Kumar
Helper V
Helper V
In response to v-shex-msft

‎10-25-2016 12:28 AM

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.

Message 6 of 16
1,501 Views
0
ankitpatira
Community Champion
Community Champion

‎10-21-2016 04:37 AM

@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.

 

Capture.PNG

Message 2 of 16
1,521 Views
0
Satish_Kumar
Helper V
Helper V
In response to ankitpatira

‎10-21-2016 04:40 AM

but the impact was not shown in the view .

 

Message 3 of 16
1,519 Views
0
Satish_Kumar
Helper V
Helper V
In response to Satish_Kumar

‎10-21-2016 04:42 AM

And if you share your knowledge to join two table and convert into one comman table.

 

Message 4 of 16
1,518 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All
Top Solution Authors
View All