Calculate table - aggragation

[Anonymous]

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

[Ashish_Mathur]

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]

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

[LivioLanzo]

@Anonymous

= 
SUMMARIZECOLUMNS(
    Sales[Company],
    "SaleAmount", SUM( Sales[Revenue] ),
    "LastDate", MAX( Sales[Sale Date] )
)

[Anonymous]

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]

@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"
)

[Anonymous]

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?