Forum Discussion

ThomasM's avatar
ThomasM
Frequent Visitor
9 years ago
Solved

Summarize Table

Hi All   I am trying to summarize a table that contains payment of customers.   I want the first transaction date and first amount, also the last transaction date and last amount.   As soon as ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi ThomasM,

     

    Please add another two <groupBy_columnName> arguments in summarize function. Please try the following formula. I test it using your given sample data and get expected result.

     

    Table = SUMMARIZE(Table21,Table21[Rim_No],"FirstTransactionDate",MIN(Table21[Payment_Date]),"Total Amont Paid",SUM(Table21[Payment_Amont]),"LastTransactionDate",MAX(Table21[Payment_Date]),"first-amont",SUMX(FILTER(Table21,Table21[Payment_Date]=MIN(Table21[Payment_Date])),Table21[Payment_Amont]),"last-amont",SUMX(FILTER(Table21,Table21[Payment_Date]=MAX(Table21[Payment_Date])),Table21[Payment_Amont]))

     



    If you have any question, please feel free to ask.

    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi ThomasM,

 

Please add another two <groupBy_columnName> arguments in summarize function. Please try the following formula. I test it using your given sample data and get expected result.

 

Table = SUMMARIZE(Table21,Table21[Rim_No],"FirstTransactionDate",MIN(Table21[Payment_Date]),"Total Amont Paid",SUM(Table21[Payment_Amont]),"LastTransactionDate",MAX(Table21[Payment_Date]),"first-amont",SUMX(FILTER(Table21,Table21[Payment_Date]=MIN(Table21[Payment_Date])),Table21[Payment_Amont]),"last-amont",SUMX(FILTER(Table21,Table21[Payment_Date]=MAX(Table21[Payment_Date])),Table21[Payment_Amont]))

 



If you have any question, please feel free to ask.

Best Regards,
Angelia

ThomasM's avatar
ThomasM
Frequent Visitor
9 years ago

Works perfectly. Thank you!!!