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 t...
Need Help
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"
)