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
Icon for Resident Rockstar rankResident 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]
)