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.

  • 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