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.

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.