Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate table - aggragation

I need to create an aggragated table based on a table within my Power BI app.

 

Basically  I have the following fields in the base tablein my Power BI app:

Company

Revenue

Sale Date

 

I want to get a Calculated table base on the table above that does the following:

Company : Group by

Revenue: Sums

Sale Date: Get the most recent sale date

 

Thanks,

Z

 

 

 

 

 

Need Help

6 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    Anonymous

     

    = 
SUMMARIZECOLUMNS(
    Sales[Company],
    "SaleAmount", SUM( Sales[Revenue] ),
    "LastDate", MAX( Sales[Sale Date] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Awesome Thanks!  So "SUMMARIZECOLUMNS" has the same effect as "CALCULATETABLE" - either of them can be used to create a new table from a current table in your Power BI app?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Sorry - left out one question. How do I add a filter to your expression - say I only want company Sales from the "East" region?

      • LivioLanzo's avatar
        LivioLanzo
        Solution Sage
        7 years ago

        Anonymous

         

        You have different Table function within DAX, CALCULATETABLE and SUMMARIZECOLUMNS are just two of them.

         

         

         

        If your region column is within your sales table then you can do:

         

        =
CALCULATETABLE(
    SUMMARIZECOLUMNS(
        Sales[Company],
        "SaleAmount", SUM( Sales[Revenue] ),
        "LastDate", MAX( Sales[Sale Date] )
    ),
    Sales[Region] = "East"
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Using the Query Editor, right click on the Company Name heading > Group By.  Select SUM as the function for Revenue and MAX as the function for Sale Date.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks but I need a new table with these aggragations not an aggregation of the current table.