Forum Discussion

lmatera's avatar
lmatera
Frequent Visitor
10 years ago
Solved

Summarize data into new table

Hello!   I'm trying to create a new table from another.   Table 1: each row is a date (from 1/1/2014 to today)     I need a table that summarizes the data:   - New table: each row ...
Need Help
  • fbrossard's avatar
    fbrossard
    10 years ago

    Hi lmatera,

     

    Do you really need to build another table with value pre calculated ?

    You could simply enrich you Power Pivot data model by  :

    1. adding calculated columns to build and year and month attributes

    Year = YEAR([Fecha]
YearMonthCode = FORMAT([Fecha],"yyyyMM")
YearMonthLabel = FORMAT([Fecha];"yyyyMMM")

    2. ordering the column YearMonthLabel by YearMonthCode

    3. adding new measures to aggregate your metrics as you want

    AveragePrecioPlatts = AVERAGEX('Test',[PrecioPlatts])
AverageEuroToDolar = AVERAGEX('Test',[PrecioPlatts] * [EuroToDolar])
AverageBrentCierre = AVERAGEX('Test';[BrentCierre]

    And just build you matrix.

greggyb's avatar
greggyb
Resident Rockstar
10 years ago

Is this for a report or to create another table in the report?

 

The matrix visualization is what you want for a report, and you can create the three measures you need with the AVERAGE() function in DAX.

 

If you need it as a separate table in your data model for some reason, then you'll use the same three measures as above in the following:

// DAX
// Calculated column in source table
Year =
YEAR( 'SourceTable'[Fecha] )

Month =
MONTH( 'SourceTable[Fecha] )


// Calculated Table
SummarizedTable =
ADDCOLUMNS(
    SUMMARIZE(
        'SourceTable'
        ,'SourceTable'[Year]
        ,'SourceTable[Month]
    )
    ,[Promedio de PrecioPlatts]
    ,[Promedio de EuroToDolar]
    ,[Promedio de BrentCierre]
)
  • ryans's avatar
    ryans
    Helper I
    10 years ago

    greggyb any advantage to doing this with DAX over creating a reference table in the Query Editor and summarizing with Group By?

    • greggyb's avatar
      greggyb
      Resident Rockstar
      10 years ago

      I've found that with large tables that Power Query is much slower at this sort of aggregation than the Tabular engine powering the data model.

       

      Functionally, no difference.

       

      Storage space / RAM use - benefit to Power Query as compression is better for non-calculated fields and tables in Tabular, but a summarized table is expected to be pretty small anyway.