Forum Discussion

Satish_Kumar's avatar
Satish_Kumar
Icon for Helper V rankHelper V
10 years ago
Solved

How to use group's in data

Help me in using Summerize command in DAX.   i am new at power bi.
  • Anonymous's avatar
    Anonymous
    10 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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:

SUMMARIZE Function (DAX)

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

 

Regards,

Xiaoxin Sheng

Satish_Kumar's avatar
Satish_Kumar
Icon for Helper V rankHelper V
10 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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

    • Satish_Kumar's avatar
      Satish_Kumar
      Icon for Helper V rankHelper V
      10 years ago

      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
      • Satish_Kumar's avatar
        Satish_Kumar
        Icon for Helper V rankHelper V
        10 years ago

        Anonymous  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